tags:

views:

283

answers:

2

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.

+2  A: 

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.

Bill Karwin
Nice - that works great. In other databases that use SQL syntax I suppose having an IF inside the SUM returning 1 or 0 given the condition should work as well.
dborba
Yes. But use `CASE` expressions if you want to be most portable. There's no `IF()` in standard SQL.
Bill Karwin
+1  A: 
SELECT Count(SomeDate)
 , (If SomeColumn >= Somedate 'BEFORE' ELSE 'AFTER') AS DateStatus
FROM sometable
GROUP BY (If SomeColumn >= Somedate 'BEFORE' ELSE 'AFTER')
IPX Ares