mySQL: Multiple COUNT()s based on differing criteria

Question

Alright, so what I'm trying to do is perform different COUNT()s on the same table based on different criteria without nested queries (efficiency) or subqueries (so it can be made into a view). Is this possible?

Example: a table has a date column - the query should be able to produce a count of the number of rows prior to & after a constant date.

Answer 1

You're using MySQL, so you can take advantage of its feature that a boolean expression evaluates to 0 for false and 1 for true. The SUM() of 1's is equal to a COUNT() where the expression is true.

SELECT SUM( date_column < '2009-08-21' ) AS prior_to_date,
       SUM( date_column > '2009-08-21' ) AS after_date
FROM MyTable;

PS: Don't try this on other brands of database that uphold the SQL standard behavior, i.e. a boolean expression yields a boolean, not a 0 or 1 integer.

Answer 2

SELECT Count(SomeDate)
 , (If SomeColumn >= Somedate 'BEFORE' ELSE 'AFTER') AS DateStatus
FROM sometable
GROUP BY (If SomeColumn >= Somedate 'BEFORE' ELSE 'AFTER')