Evening all!
I'm after a bit of advice with a query I'm writing as I'm not sure if it's possible..
I want to return a list of numbers representing the hour in the day, showing the value of the record if it exists and 0 if it doesn't.
Table (stats_pagetraffic):
page_id | timestamp | count
1 | 2009-08-18 13:00:00 | 1
1 | 2009-08-18 16:00:00 | 1
1 | 2009-08-18 17:00:00 | 5
1 | 2009-08-18 18:00:00 | 2
Query so far:
Currently returns
pagetraffic = 1,1,5,2
I want it to return
pagetraffic = 0,0,0,0,0,0,0,0,0,0,0,0,0,1,0,0,1,5,2,0,0,0,0,0
where the 0's are hours that don't exist in the stats_pagetraffic table
Any ideas?
I'm after a bit of advice with a query I'm writing as I'm not sure if it's possible..
I want to return a list of numbers representing the hour in the day, showing the value of the record if it exists and 0 if it doesn't.
Table (stats_pagetraffic):
page_id | timestamp | count
1 | 2009-08-18 13:00:00 | 1
1 | 2009-08-18 16:00:00 | 1
1 | 2009-08-18 17:00:00 | 5
1 | 2009-08-18 18:00:00 | 2
Query so far:
Code:
SELECT GROUP_CONCAT(`stats_pagetraffic`.`count`) AS `pagetraffic` FROM `stats_pagetraffic` WHERE `stats_pagetraffic`.`page_id`='1' AND `stats_pagetraffic`.`timestamp`>='20090818000000' AND `stats_pagetraffic`.`timestamp`<='20090818230000' ORDER BY `stats_pagetraffic`.`timestamp` ASC
Currently returns
pagetraffic = 1,1,5,2
I want it to return
pagetraffic = 0,0,0,0,0,0,0,0,0,0,0,0,0,1,0,0,1,5,2,0,0,0,0,0
where the 0's are hours that don't exist in the stats_pagetraffic table
Any ideas?
Last edited: