check field1 and field2 from single table

Question

Hello, In SQL: I want to check. From single table,

 if(field1 = 1 and DATE_SUB(CURDATE(),INTERVAL 7 DAY) <= field2) then
        return count(*)
     else
        return "false"

thanks v.srinath

Answer 1

SELECT CASE WHEN (field1 = 1 AND DATE_SUB(CURDATE(),INTERVAL 7 DAY) <= field2) 
                THEN   COUNT(*)     
       ELSE 0 
       END  
FROM SomeTable

Answer 2

CASE  
    WHEN field1 = 1
    AND  DATE_SUB(CURDATE(),INTERVAL 7 DAY) <= field2
    THEN COUNT(*)
    ELSE 'false'
END

Note that this only works in the SELECT list, or HAVING clause, and you should have a GROUP BY clause on field1 and field2. The reason is that COUNT() is an aggregate function. Because field1 and field2 appear outside an aggregatee function, aggregation for those fields must be enforced with a GROUP BY clause. Most RDBMS-es won't allow you to write this unless there is an appropriate GROUP BY clause, but MySQL will, and will silently return rubbish for those fields.

BTW - I think you should probably not write "false" but simply FALSE - right now both legs of the choice have a different type (boolean vs string). The db will probably coerce types, but it is still bad practice. You should ensure that the expression for each brach of the CASE expression have the same data type.