CODEWITHSUNDEEP
mysql
carol1287
carol1287

Reputation: 395

MySql How to get hourly average count

I have been looking at several different questions related to hourly average queries but I could not find any that addresses the following.

I have a log table that keeps track on how many times a page is accessed by a user:

ID   USERID   PAGEID  SECNO    DATE

1    123      120     14      6/08/2013 10:07:29 AM
1    124      438     1       6/08/2013 11:00:01 AM
1    123      211     18      6/09/2013 14:07:59 PM
1    123      120     14      6/10/2013 05:07:18 PM
1    124      312     4       6/10/2013 08:04:32 PM
1    128      81      54      6/11/2013 07:02:15 AM

and I am trying to get two different queries. One that looks like this:

HOURLY      Count     Average   

12am        0        0
1am         0        0
2am         0        0
3am         0        0
4am         0        0
5am         1        0
6am         0        0
7am         1        0
8am         0        0
9am         0        0
10am        1        0
11am        1        0
12pm        0        0
1pm         0        0
2pm         1        0
3pm         0        0
4pm         0        0
5pm         1        0
6pm         0        0
7pm         0        0
8pm         1        0
9pm         0        0
10pm        0        0
11pm        0        0

The second query like this:

DAY      PERCENTAGE

Monday   10%
Tuesday   16%
Wednesday   14%
Thursday   22%
Friday   21%
Saturday   14%
Sunday   3%

**Please notice that the average value is just a sample

So far for the first query I have something like this:

SELECT 
   HOUR(date) AS hourly, 
   Count(*)  
FROM 
   logs
GROUP BY 
   hourly

I tried adding AVG() after Count() but did not work.

My log table does not have data for every single hour but i still need to display all the hours on my report. if hour empty, then value 0. Any ideas how could I achieve that?

Upvotes: 1

Views: 1917

Answers (2)

flaschenpost
flaschenpost

Reputation: 2235

SQL has no way to "create" an hour out of nothing. So the simple trick is to have a table numbers (number int) with the numbers you need (may be 1- 31 to be ready for month, or 1-366 for year). That table you can left join with your data in the kind of 

select n.number as hour, count(*) as cnt 
from numbers as n
left join logtable as l
on hour(l.date) = n.number
group by n.number

You could "simulate" it without a table, but there are several occasions where that table is helpful.

Upvotes: 2

Stephan
Stephan

Reputation: 8090

Try this for the first query:

SELECT
    h.hour,
    IFNULL(tmp.the_count,0),
    IFNULL(tmp.the_avg,0)
FROM 
    hourly h
LEFT JOIN (
    SELECT
        hourly,
        SUM(visits) the_count,
        SUM(visits)/COUNT(DISTINCT userid) as the_avg
    FROM (
        SELECT 
           HOUR(date) AS hourly, 
           COUNT(*) as visits,
           userid
        FROM 
           logs
        GROUP BY 
           hourly,
           userid
    ) as tmp
    GROUP BY
        hourly
) as tmp
    ON tmp.hourly = h.hour

Try this for the second query:

SELECT
    theday,
    IFNULL(percentage,0) as percentage
FROM (
    SELECT  DATE_FORMAT('2013-06-16','%W') as theday UNION
    SELECT  DATE_FORMAT('2013-06-16' - INTERVAL 1 DAY,'%W') as theday UNION
    SELECT  DATE_FORMAT('2013-06-16' - INTERVAL 2 DAY,'%W') as theday UNION
    SELECT  DATE_FORMAT('2013-06-16' - INTERVAL 3 DAY,'%W') as theday UNION
    SELECT  DATE_FORMAT('2013-06-16' - INTERVAL 4 DAY,'%W') as theday UNION
    SELECT  DATE_FORMAT('2013-06-16' - INTERVAL 5 DAY,'%W') as theday UNION
    SELECT  DATE_FORMAT('2013-06-16' - INTERVAL 6 DAY,'%W') as theday 
) as weekt
LEFT JOIN (
    SELECT 
        DATE_FORMAT(date,'%W') AS daily, 
        (COUNT(*)/(SELECT COUNT(*) FROM logs))/100 as percentage
    FROM 
        logs 
    WHERE 
        date >= '2013-06-10'
        AND date <= '2013-06-16'
    GROUP BY 
        daily
) as logdata
    ON logdata.daily = weekt.theday

Upvotes: 4

Related Questions