MySQL output hours in a day

[toastyman]

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:

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?

 

[tonyyeb]

Not sure it can be done in the SQL statement as checking for a record that isn't there whilst returning records that are there is tricky.

Is this going into a webpage/application? What programming language you using?

 

[toastyman]

I'm wondering if it can be done by having a subquery which lists the timestamps for 0am to 23pm. You could then do some sort of join to lookup the dates in the statistics table. The problem there is I have no idea how to go about writing that subquery to list the timestamps!

I'm using PHP but i'm quite resistant against using it for this as it's not just going to return data for one page.. it'll be for about 20. That's why i'd like to do as much of it as possible in the database in as few queries as possible.

 

[tonyyeb]

I'd be tempted to do it in PHP and then put that calculation into a page that can be included in the other pages.

As for doing a query within a query... that is a possibility but like you I can see it in my head but putting it into a query is failing me at the moment!!

 

[SimonCHere]

Clunkyness below!

SELECT bob.timestamp,Max(bob.cnt) as pagetraffic
  FROM  (select convert(datetime,'2009-08-18 12:00:00',120) as timestamp, 0 as cnt
        union 
        select convert(datetime,'2009-08-18 13:00:00',120) as timestamp, 0 as cnt
        union 
        select convert(datetime,'2009-08-18 14:00:00',120) as timestamp, 0 as cnt
        union
        select convert(datetime,'2009-08-18 15:00:00',120) as timestamp, 0 as cnt
        union 
        select s.timestamp ,s.cnt from stats_pagetraffic s where s.page_id = 1) as bob
group by bob.timestamp

Sorry MSSQL doesn't have the GROUP_CONCAT function.

output
timestamp	pagetraffic
18/08/2009 12:00:00	0
18/08/2009 13:00:00	1
18/08/2009 14:00:00	0
18/08/2009 15:00:00	0
18/08/2009 16:00:00	1
18/08/2009 17:00:00	5
18/08/2009 18:00:00	2

I should have noted that all the union stuff was because I didn't want to create a table for the "missing" hours. you could just have had

SELECT bob.timestamp,Max(bob.cnt) as pagetraffic
  FROM  (select timestamp, 0 as cnt from [SomeTableWith24RowsInItForTheHours]
             union
            select s.timestamp ,s.cnt from stats_pagetraffic s where s.page_id = 1) as bob
group by bob.timestamp

 

[toastyman]

Fantastic, did the trick nicely! Just got to remember to have all the hours in the other table..

Here's the final query..

 SELECT bob.timestamp, Max( bob.count ) AS pagetraffic
FROM (
SELECT timestamp, 0 AS count
FROM stats_hours
UNION
SELECT s.timestamp, s.count
FROM stats_pagetraffic s
WHERE s.page_id =61
AND s.timestamp >= '20090819000000'
AND s.timestamp <= '20090819230000'
) AS bob
GROUP BY bob.timestamp
ORDER BY bob.timestamp ASC

Thanks to you both!

Next step is to get it working with GROUP_CONCAT.. when I put GROUP_CONCAT(Max(bob.count)) it gives an 'Invalid use of group function' error. Perhaps I'm implementing it wrong..

 

[SimonCHere]

Glad it worked for you. I can't help with the GROUP_CONCAT bit as I don't have access to mySQL.

You don't have to use 'bob' as the table alias though...

 

[toastyman]

I quite liked the bob bit which is why I kept it! Absolutely stumped on the group_concat.. struggling to try and find a workaround

 

[SimonCHere]

in SQL you can do something like:

declare @combined varchar(2000)
select @combined = isnull(@combined + ', ','') + Max( bob.count ) AS pagetraffic
FROM (
SELECT timestamp, 0 AS count
FROM stats_hours
UNION
SELECT s.timestamp, s.count
FROM stats_pagetraffic s
WHERE s.page_id =61
AND s.timestamp >= '20090819000000'
AND s.timestamp <= '20090819230000'
) AS bob
GROUP BY bob.timestamp
ORDER BY bob.timestamp ASC

But I'm not sure that will help you.

How about

SELECT GROUP_CONCAT(`sub1`.`pagetraffic`) AS `pagetraffic` FROM 
(SELECT bob.timestamp, Max( bob.count ) AS pagetraffic
FROM (
SELECT timestamp, 0 AS count
FROM stats_hours
UNION
SELECT s.timestamp, s.count
FROM stats_pagetraffic s
WHERE s.page_id =61
AND s.timestamp >= '20090819000000'
AND s.timestamp <= '20090819230000'
) AS bob
GROUP BY bob.timestamp) as sub1
ORDER BY sub1.timestamp ASC

 

[toastyman]

That last one worked great, thanks very much!

Just to be a real pain i've created another small problem. I've taken that query and added it to my other query.. the theory with this one is that it wouldn't just return the records for one page but would now return the records for lots of pages.

SELECT `sec`.`name`,`sec`.`published`,`sec`.`contentupdated`,`sec`.`id`, (
        SELECT GROUP_CONCAT(`sub1`.`pagetraffic3`) AS `pagetraffic2`
        FROM (
                SELECT bob.timestamp, Max( bob.count ) AS pagetraffic3
                FROM (
                        SELECT timestamp, 0 AS count
                        FROM stats_hours
                        UNION
                        SELECT s.timestamp, s.count
                        FROM stats_pagetraffic s
                        WHERE s.page_id =`sec`.`id`
                        AND s.timestamp >= '20090821000000'
                        AND s.timestamp <= '20090821230000'
                ) AS bob
                GROUP BY bob.timestamp) as sub1
                ORDER BY sub1.timestamp ASC
        ) AS `pagetraffic`, (
        SELECT SUM(`stats_pagetraffic`.`count`)
        FROM `stats_pagetraffic`
        WHERE `stats_pagetraffic`.`page_id`=`sec`.`id` AND `stats_pagetraffic`.`timestamp`>='20090821000000' AND `stats_pagetraffic`.`timestamp`<='20090821230000'
) AS `count`
FROM `sections` AS `sec`
ORDER BY `count` DESC  LIMIT 5

Line 11 is the problem - WHERE s.page_id =`sec`.`id` (`sec`.`id` is an unknown column)
I presume this is because you can't pass values between multiple sub queries? The only way I can think to get around this is to write it all as one query using joins, but then that would mean rewriting the original query to work in a different way.

Gah!