Can I do three counts in one query?

Databases
1

I have currently three queries that I’m running to find three counts. They are supposed to be counting messages that are old, for today and for the future. I am wondering if there is a way to do the same thing in one query. Here are the queries:

SELECT COUNT() FROM messages WHERE userId = 1 AND show_date < today
SELECT COUNT() FROM messages WHERE userId = 1 AND show_date = today
SELECT COUNT(*) FROM messages WHERE userId = 1 AND show_date > today

*today is to represent today’s date only, not literally in the query.

I know I could group by the show_date and filter the results outside of MySQL, but I wanted to know if there was a better way.

2 
SELECT COUNT(CASE WHEN show_date < CURRENT_DATE
                  THEN 'ok' ELSE NULL END) AS old
     , COUNT(CASE WHEN show_date = CURRENT_DATE
                  THEN 'ok' ELSE NULL END) AS today
     , COUNT(CASE WHEN show_date > CURRENT_DATE
                  THEN 'ok' ELSE NULL END) AS future
  FROM messages 
 WHERE userId = 1
3

Wow. Cool. I’ll try that instead. You put a lot in that query that I haven’t seen before, so clearly I have some reading to do. Thank you so much.

4

If show_date has an index then will it be used for this query? If not then it might be more efficient to use separate queries if the table is large.

5

lemon, if show_date does ~not~ have an index, then doing three separate queries will require 3 times as long as my query, which will require only one pass of the rows

on the other hand, if it ~does~ have an index, that index might get ignored anyway, because of the WHERE clause on userid

but my query will still examine all the rows in only one pass, even if it’s an index search on the userid index

6

Thanks, I was only wondering whether there’s any chance your query use an index - I thought if that were true then I gained some new secret knowledge about mysql :slight_smile: