CODEWITHSUNDEEP
mysqldate
aphextwix
aphextwix

Reputation: 1858

Group by hour showing breakdown for each day within date range MYSQL

I'm trying to create a query to show the breakdown of leads relating to finance contracts grouped by hour.

Here's an example of what I have so far for hours: 

SELECT CONCAT(HOUR(received), ':00-', HOUR(received)+1, ':00') AS Hours, 
COUNT(*) as `leads`
FROM (digital_lead)
WHERE `received` BETWEEN '2014-11-01 00:00:00' AND '2014-11-24 00:00:00'
GROUP BY HOUR(received)

And here's the result ....

 Hours   usage

0:00-1:00   36
1:00-2:00   25
2:00-3:00   16
3:00-4:00   4
4:00-5:00   7
5:00-6:00   8
6:00-7:00   13 // etc all the way to 23:00 - 24:00

OK - so that seems to work, however, it aggregates all of the leads for that time slot over the course of the period set in the BETWEEN statement. I would like to be able to show the breakdown per hour for each day within the period range.

So that would look something like: 

Date        Hours         leads
2014-11-01      
            0:00-1:00      36
            1:00-2:00      25
            2:00-3:00      16
            3:00-4:00      4
            4:00-5:00      7
            5:00-6:00      8
            6:00-7:00      13 // etc all the way to 23:00 - 24:00

So each date would be displayed and then the hours for that day and so forth.

Thanks.

Upvotes: 0

Views: 780

Answers (1)

Marc B
Marc B

Reputation: 360562

Group by date as well:

SELECT DATE(received), HOUR(received), ...
...
GROUP BY DATE(received), HOUR(received)

Since you're grouping by hour() only, you'll essentialy be getting

Hour
1      sum of leads in hour 1 of jan 1, jan 2, jan 3, ... dec 31
2      sum of leads in hour 2 of jan 1, jan 2, jan 3, ... dec 31

Rather than

Jan 1  Hour 1  sum of leads for just that one hour on the one day
Jan 1  Hour 2  sum of leads for just that one hour on the one day
..
Dec 31  Hour 23 sum of leads

Upvotes: 2

Related Questions