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.

Bill Karwin · Accepted Answer · 2009-08-21 19:34:29Z

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.

answered Aug 21, 2009 at 19:34

Bill Karwin

567k87 gold badges709 silver badges869 bronze badges

Sign up to request clarification or add additional context in comments.

4 Comments

dborba Over a year ago

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.

Bill Karwin Over a year ago

Yes. But use CASE expressions if you want to be most portable. There's no IF() in standard SQL.

Senthil Muthiah Over a year ago

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.

Bill Karwin Over a year ago

@SenthilMuthiah, it's hard to guess what your conditions are, but I guess you have WHERE <term1> OR <term2>. 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.

Collectives™ on Stack Overflow

mySQL: Multiple COUNT()s based on differing criteria

1 Answer 1

4 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

4 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related