MySql query result all hours of day

Question

I need to get data for all hours of day, even if the count is 0. Right now it outputs:

clicks--hour
 1----- 7
 2----- 13

My query right now:

SELECT count(*) as clicks, hour(time) as hour FROM clicks WHERE DATE(time) = DATE(NOW()) GROUP BY hour

Thank you for your time

#

Solved, managed to do it on my own, some how.

SELECT h.Hour
     , COUNT(id) AS clicks
  FROM ( SELECT 0 AS Hour
         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 
         UNION ALL SELECT 10 
         UNION ALL SELECT 11
         UNION ALL SELECT 12
         UNION ALL SELECT 13
         UNION ALL SELECT 14
         UNION ALL SELECT 15
         UNION ALL SELECT 16
         UNION ALL SELECT 17
         UNION ALL SELECT 18
         UNION ALL SELECT 19
         UNION ALL SELECT 20
         UNION ALL SELECT 21
         UNION ALL SELECT 22
         UNION ALL SELECT 23) AS h
LEFT OUTER
  JOIN clicks
    ON hour(time) = h.Hour
   AND DATE(time) = date(now())
GROUP 
    BY h.Hour

possible duplicate of [MySQL how to fill missing dates in range?](http://stackoverflow.com/questions/3538858/mysql-how-to-fill-missing-dates-in-range) — Jeremy Smyth, Sep 15 '13 at 13:33
I tried to google before asking here and all the answers are too complicated for me, sorry. — user2770029, Sep 15 '13 at 13:35

score 0 · Answer 1 · answered Sep 15 '13 at 13:36

0

You can use the COALESCE This command helps in the case that a select returns null, to replace null with a value like this:

SELECT 
    COALESCE(count(*), 0) as clicks, 
    COALESCE(hour(time), hour(now())) as hour 
FROM 
    clicks 
WHERE DATE(time) = DATE(NOW()) GROUP BY hour

Hope that's what you're looking for.

answered Sep 15 '13 at 13:36

Not Amused

942
2
10
28

thanks for help, but this won't do the trick because if clicks is 0 then there won't be hour record either – user2770029 Sep 15 '13 at 13:39
What about with 2 coalesces? If both fields return `null` then it will replace clicks with 0 and hour with current hour, that will fake a row. – Not Amused Sep 15 '13 at 13:41
Can you specify with "some reason"? Tell us anything wrong after you executed the code – edisonthk Sep 15 '13 at 13:49
Thank you everyone for your help, I fixed it some how. – user2770029 Sep 15 '13 at 13:58

MySql query result all hours of day

1 Answers1

Linked