MySQL GROUP BY with Zero values in the count

Question

I have following query which i need the count of the number of user who has the same golddate as the same day, however since i have to use the GROUP BY it doesnt return the zero values when there is no one on that day, can you please help ?

SELECT count( * ) AS total, DATE( `golddate` ) AS gold_date
                FROM `user`
                WHERE YEAR( `golddate` ) >=2014
                GROUP BY DATE( `golddate` )
                ORDER BY DATE( `golddate` ) ASC

I want to show something like

goldate | total

2012-12-10 | 23
2012-10-12 | 0

but issue is it never returns the zero value dates, due to the group by

I tried following but no use,

SELECT
  u1.golddate
  -- this will count the number of links to each word
  -- if there are no links the COUNT() call will return 0
  , COUNT(u2.golddate) AS linkCount
FROM user u2
  LEFT JOIN user u1
    ON u1.user_id = u2.user_id
      OR u2.user_id = u1.user_id
GROUP BY u1.golddate

Nor even this doesnt work

SELECT COALESCE(COUNT(`golddate`), 0) AS total, DATE( `golddate` ) AS gold_count
                FROM `user`
                WHERE YEAR( `golddate` ) >=2014
                GROUP BY DATE( `golddate` )
                ORDER BY DATE( `golddate` ) ASC

There will be no record if you don't have that day in your DB. — juergen d, Jan 26 '14 at 11:07
You don't understand - there will be nothing to show, not even zero. Since there is no record, there is no converting it to zero. — juergen d, Jan 26 '14 at 11:09
Issues of data display are generally best handled at the application level - e.g. a simple php loop. Alternatively you can construct a UNION or utility table to replicate this behaviour. All widely discussed. — Strawberry, Jan 26 '14 at 11:11
it can be done, if the count value is zero i want to show as zero should be able to do, — mahen3d, Jan 26 '14 at 11:12
@LukasEder - yeah, but that particular question doesn't have the best explanation for _why_ it works, and he needs something a little broader here. mahen3D - you need something known as a Calendar table - look around to find examples. — Clockwork-Muse, Jan 26 '14 at 13:53

score 1 · Accepted Answer · edited May 23 '17 at 12:09

As others have mentioned, you will need to artificially create the dates for the missing data.

Using RedFilter's cross join date generation code here, here's how you can create all dates in a given range, and then LEFT JOIN back to your real data. COUNT(*) needs to be changed to something in the user table so as not to count the dummy dates.

SELECT count(u.golddate) AS total, a.Date AS golddate
FROM 
(
    select curdate() - INTERVAL (a.a + (10 * b.a) + (100 * c.a)) DAY as Date
    from (select 0 as a union all select 1 union all select 2 union all select 3 union all select 4 union all select 5 union all select 6 union all select 7 union all select 8 union all select 9) as a
    cross join (select 0 as a union all select 1 union all select 2 union all select 3 union all select 4 union all select 5 union all select 6 union all select 7 union all select 8 union all select 9) as b
    cross join (select 0 as a union all select 1 union all select 2 union all select 3 union all select 4 union all select 5 union all select 6 union all select 7 union all select 8 union all select 9) as c
) a  

LEFT OUTER JOIN `user` u
  ON DATE(u.golddate) = a.Date

WHERE a.Date between '2013-01-01' and '2013-01-10'

GROUP BY a.Date
ORDER BY a.Date ASC;

SqlFiddle here

MySQL GROUP BY with Zero values in the count

1 Answers1