sql show all dates on count query

Question

I have a question about my mysql query. I want to count the number of records per day. That works, but I also want to see the dates on which the count is 0. The dates where the total is 0 is not in the resultset. How do I do that? This is my current query:

SELECT COUNT(id) AS total_per_day, 
DATE_FORMAT(reg_date, "%Y-%m-%d") as my_date 
FROM my_table GROUP BY my_date

Thanks in advance for your replies.

Have a look at this post : http://stackoverflow.com/questions/9295616/how-to-get-list-of-dates-between-two-dates-in-mysql-select-query — BMN, Apr 03 '12 at 08:08

Pablo Martinez · Answer 1 · 2012-04-04T06:39:53.490

0

try a self join:

SELECT COUNT(t.id) AS total_per_day, DATE_FORMAT(t.reg_date, "%Y-%m-%d") as my_date 
FROM my_table t left outher join my_table t2 on t.id=t2.id GROUP BY t.my_date

edited Apr 04 '12 at 06:39

answered Apr 03 '12 at 08:42

Pablo Martinez

2,172
1
23
27

score 0 · Accepted Answer · answered Apr 03 '12 at 11:56

Try this query -

-- Create this temp. table firstly!
-- CREATE TABLE days(d int);
-- INSERT INTO days(d) VALUES (0), (1), (2), (3), (4), (5), (6), (7), (8), (9);

SELECT t.day, COUNT(mt.id) total_per_day FROM (
  SELECT
    min_date + INTERVAL t1.d + t2.d * 10 DAY day,
    min_date,
    max_date
  FROM days t1, days t2,
    (SELECT MIN(DATE(reg_date)) min_date, MAX(DATE(reg_date)) max_date FROM my_table) t3
  HAVING day BETWEEN min_date AND max_date) t
  LEFT JOIN my_table mt
    ON t.day = DATE(mt.reg_date)
GROUP BY t.day

sql show all dates on count query

2 Answers2