How to do multiple counts in a MYSQL select query?

Question

SELECT count(item_id) as today FROM table1 
WHERE DATE(creation_date) > DATE(NOW() - INTERVAL 1 DAY);

SELECT count(item_id) as yesterday from table1
where DATE(creation_date) = DATE(NOW() - INTERVAL 1 DAY);

SELECT count(item_id) year_period from table1
where creation_date > now() - interval 1 year;

I have the following queries, I would like to get the results returned from these queries in one row. as shown below:

----------------------------------------
|today |  yesterday  | one_year_period |
----------------------------------------
|   2  |    5        |        7        |
----------------------------------------

I am award of a few way to accompliish this but I would like a more effecient solution to accomplish what I want. I can accompliish what I want by using a union as seen here

SELECT count(item_id) as today FROM table1 
WHERE DATE(creation_date) > DATE(NOW() - INTERVAL 1 DAY)
union
SELECT count(item_id) as yesterday from table1
where DATE(creation_date) = DATE(NOW() - INTERVAL 1 DAY)
union
SELECT count(item_id) year_period from table1
where creation_date > now() - interval 1 year;

I would like to optimize my solution so that the query is not repeated on 3 seperate occasions.

I made a modification to the query to use the SUM(CASE()) and I solved the issue that I was having. — Andre Reid
– Andre Reid, Commented Nov 10, 2017 at 20:35
DATE(NOW() - INTERVAL 1 DAY) can be simplified to CURDATE() - INTERVAL 1 DAY. — Rick James
– Rick James, Commented Nov 11, 2017 at 1:39

JNevill · Accepted Answer · 2017-11-10 20:29:03Z

1

Using CASE statements:

SELECT 
    SUM(CASE WHEN DATE(creation_date) > DATE(NOW() - INTERVAL 1 DAY) THEN 1 END) as today,
    SUM(CASE WHEN DATE(creation_date) = DATE(NOW() - INTERVAL 1 DAY) THEN 1 END) AS yesterday,
    SUM(CASE WHEN creation_date > now() - interval 1 year THEN 1 END) AS year_period
FROM table1

answered Nov 10, 2017 at 20:29

JNevill

50.6k4 gold badges46 silver badges72 bronze badges

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

1 Comment

Juan Carlos Oropeza Over a year ago

You need ELSE 0 on your CASE or do like me and use COUNT() to ignore NULLs

Andre Reid · Accepted Answer · 2017-11-10 20:33:49Z

0

SELECT 
   SUM(CASE WHEN DATE(creation_date) > DATE(NOW() - INTERVAL 1 DAY) THEN 1 ELSE 0 END) today,
   SUM(CASE WHEN DATE(creation_date) = DATE(NOW() - INTERVAL 1 DAY) THEN 1 ELSE 0 END) yesterday,
   SUM(CASE WHEN date(creation_date) > now() - interval 1 year THEN 1 ELSE 0 END) last_year
FROM table1

answered Nov 10, 2017 at 20:33

Andre Reid

952 silver badges12 bronze badges

Comments

O. Jones · Accepted Answer · 2017-11-10 20:36:57Z

If you're looking for efficiency, do this, to avoid saying DATE(creation_date), and to use COUNT(*) instead of COUNT(column). That way you can use an index on your creation_date column.

 SELECT count(*) as today 
   FROM table1 
  WHERE creation_date >= CURDATE()

And, if you have that index and use the sort of pattern I showed, something like your original query will be far faster than a bunch of SUM(CASE...)) terms. Why? because MySQL can satisfy the query just from doing so-called loose index scans. Loose index scans are astonishingly fast.

SELECT count(*) as today FROM table1 
WHERE creation_date >= CURDATE()
union
SELECT count(*) as yesterday
 from table1
where creation_date >= CURDATE() - INTERVAL 1 DAY
  and creation_date <  CURDATE()
union
SELECT count(*) year_period from table1
where creation_date >= CURDATE() - interval 1 year;

Collectives™ on Stack Overflow

How to do multiple counts in a MYSQL select query?

3 Answers 3

1 Comment

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

1 Comment

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related