MySQL GROUP BY with Zero values in the count

mahen3d Published at Dev

mahen3d

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

StuartLC

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

Collected from the Internet

Please contact [email protected] to delete if infringement.