13

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.

dborba
  • 653
  • 6
  • 15

1 Answers1

34

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
  • 538,548
  • 86
  • 673
  • 828
  • 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 Aug 21 '09 at 19:41
  • 1
    Yes. But use `CASE` expressions if you want to be most portable. There's no `IF()` in standard SQL. – Bill Karwin Aug 21 '09 at 19:43
  • I tried with multiple and seperate. The problem i found if i put seperate select statement with necessary where clause, then mysql uses the index , but when combined, it does not use the index. Checked using explain. – Senthil Muthiah Aug 06 '14 at 13:04
  • @SenthilMuthiah, it's hard to guess what your conditions are, but I guess you have `WHERE OR `. This spoils the use of an index on either column, because if you have an index for term1, the query must do a table-scan to find all rows for term2, and vice-versa. – Bill Karwin Aug 06 '14 at 16:28