CODEWITHSUNDEEP
mysql
aphextwix
aphextwix

Reputation: 1858

MYSQL Group by Hour - showing zero results

I'm trying to generate a query which returns results by hour and which also shows zero results.

At the moment MYSQL statement only shows results where results exist rather than existing results and zero results.

MYSQL SO FAR

SELECT DATE(received) as Date, 
CONCAT(HOUR(received), ':00-', HOUR(received)+1, ':00') AS Hours, 
IFNULL(COUNT(*), 0) as Leads from digital_lead
where `received` BETWEEN '2014-11-01' AND '2014-11-25'
GROUP BY DATE(received), HOUR(received)

Currently this shows each day within the date range divided by hours, only where exists a lead for that hour-slot. I would like to show all of the hours within each day, even where no lead data exists for that hour.

Example

DATE         HOUR SLOT         LEADS 
2014-11-01   00:00 - 01:00      0
             01:00 - 02:00      0
             02:00 - 03:00      12 ... etc

I've had a look around on Stackoverflow and found this question which seems similar

LINK

From what I understand I need to create a separate table with 24 hour slots and JOIN that to my query to show IFNULL(COUNT(*),0) results in my query.

Can somebody please demonstrate how this would work in practice?

UPDATE

Using Strawberry's suggestions, below here is what I have now: 

SELECT CONCAT(CURDATE(),' 00:00:00') + INTERVAL i2.i*10+i1.i HOUR today
      , COUNT(received) total
   FROM ints i1
  CROSS
   JOIN ints i2   
   LEFT
   JOIN digital_lead
     ON DATE_FORMAT(received,'%Y-%m-%d %h:00:00') = CONCAT(CURDATE(),' 00:00:00') + INTERVAL i2.i*10+i1.i HOUR
  WHERE received BETWEEN '2014-11-01 00:00:00' AND '2014-11-26 00:00:00'
  GROUP
     BY HOUR(received)

However, this isn't showing any results where I know there are some ...?

Upvotes: 0

Views: 760

Answers (1)

Strawberry
Strawberry

Reputation: 33945

This isn't necessarily the solution that I'd advocate, but you asked to see an example along these lines...

I have a table of integers (0-9). I can join that to a string to make a list, like so...

 SELECT CONCAT(CURDATE(),' 00:00:00') + INTERVAL i HOUR today FROM ints;
 +---------------------+
 | today               |
 +---------------------+
 | 2014-11-26 00:00:00 |
 | 2014-11-26 01:00:00 |
 | 2014-11-26 02:00:00 |
 | 2014-11-26 03:00:00 |
 | 2014-11-26 04:00:00 |
 | 2014-11-26 05:00:00 |
 | 2014-11-26 06:00:00 |
 | 2014-11-26 07:00:00 |
 | 2014-11-26 08:00:00 |
 | 2014-11-26 09:00:00 |
 +---------------------+

If I want more than 10 items in my list, I can just join the integers multiple times, like so...

 SELECT CONCAT(CURDATE(),' 00:00:00') + INTERVAL i2.i*10+i1.i HOUR today FROM ints i1,ints i2 HAVING DATE(today) = DATE(CURDATE());
 +---------------------+
 | today               |
 +---------------------+
 | 2014-11-26 00:00:00 |
 | 2014-11-26 01:00:00 |
 | 2014-11-26 02:00:00 |
 | 2014-11-26 03:00:00 |
 | 2014-11-26 04:00:00 |
 | 2014-11-26 05:00:00 |
 | 2014-11-26 06:00:00 |
 | 2014-11-26 07:00:00 |
 | 2014-11-26 08:00:00 |
 | 2014-11-26 09:00:00 |
 | 2014-11-26 10:00:00 |
 | 2014-11-26 11:00:00 |
 | 2014-11-26 12:00:00 |
 | 2014-11-26 13:00:00 |
 | 2014-11-26 14:00:00 |
 | 2014-11-26 15:00:00 |
 | 2014-11-26 16:00:00 |
 | 2014-11-26 17:00:00 |
 | 2014-11-26 18:00:00 |
 | 2014-11-26 19:00:00 |
 | 2014-11-26 20:00:00 |
 | 2014-11-26 21:00:00 |
 | 2014-11-26 22:00:00 |
 | 2014-11-26 23:00:00 |
 +---------------------+

If you don't like the overhead of maintaining a utility table (some people seem not to - weird), you can also substutute the utility table with a list that you create on the fly - SELECT 0 UNION SELECT 1 UNION SELECT 2, etc.

Now you simply OUTER JOIN your source data, something like this...

 SELECT * FROM my_table;
 +---------------------+
 | dt                  |
 +---------------------+
 | 2014-11-26 03:15:35 |
 | 2014-11-26 03:17:32 |
 | 2014-11-26 04:19:01 |
 | 2014-11-26 09:44:11 |
 | 2014-11-26 09:54:00 |
 | 2014-11-26 09:59:30 |
 | 2014-11-26 12:07:24 |
 | 2014-11-26 12:07:26 |
 | 2014-11-26 13:01:15 |
 | 2014-11-26 17:00:00 |
 +---------------------+


  SELECT CONCAT(CURDATE(),' 00:00:00') + INTERVAL i2.i*10+i1.i HOUR today
       , COUNT(dt) total
    FROM ints i1
   CROSS
    JOIN ints i2
    LEFT
    JOIN my_table x
      ON DATE(dt) = DATE(CONCAT(CURDATE(),' 00:00:00') + INTERVAL i2.i*10+i1.i HOUR)
     AND HOUR(dt) = HOUR(CONCAT(CURDATE(),' 00:00:00') + INTERVAL i2.i*10+i1.i HOUR)
   WHERE DATE(CONCAT(CURDATE(),' 00:00:00') + INTERVAL i2.i*10+i1.i HOUR) = CURDATE()
   GROUP
      BY CONCAT(CURDATE(),' 00:00:00') + INTERVAL i2.i*10+i1.i HOUR;
 +---------------------+-------+
 | today               | total |
 +---------------------+-------+
 | 2014-11-26 00:00:00 |     0 |
 | 2014-11-26 01:00:00 |     0 |
 | 2014-11-26 02:00:00 |     0 |
 | 2014-11-26 03:00:00 |     2 |
 | 2014-11-26 04:00:00 |     1 |
 | 2014-11-26 05:00:00 |     0 |
 | 2014-11-26 06:00:00 |     0 |
 | 2014-11-26 07:00:00 |     0 |
 | 2014-11-26 08:00:00 |     0 |
 | 2014-11-26 09:00:00 |     3 |
 | 2014-11-26 10:00:00 |     0 |
 | 2014-11-26 11:00:00 |     0 |
 | 2014-11-26 12:00:00 |     2 |
 | 2014-11-26 13:00:00 |     1 |
 | 2014-11-26 14:00:00 |     0 |
 | 2014-11-26 15:00:00 |     0 |
 | 2014-11-26 16:00:00 |     0 |
 | 2014-11-26 17:00:00 |     1 |
 | 2014-11-26 18:00:00 |     0 |
 | 2014-11-26 19:00:00 |     0 |
 | 2014-11-26 20:00:00 |     0 |
 | 2014-11-26 21:00:00 |     0 |
 | 2014-11-26 22:00:00 |     0 |
 | 2014-11-26 23:00:00 |     0 |
 +---------------------+-------+

Remember, I said I wouldn't necessarily advocate this as a solution ;-)

Upvotes: 0

Related Questions