CODEWITHSUNDEEP
mysql
Joey.Z
Joey.Z

Reputation: 4760

Count the amount of visits per hour from a visitlogs table

I have a table recording some visits information of some locations along with the time and maybe some comments on this place which is like:

+---------+---------+------------+-----------------------+---------------------+
| visitId | userId  | locationId | comments              | time                |
+---------+---------+------------+-----------------------+---------------------+
|       1 |    3    |     12     | It's a good day here! | 2012-12-12 20:50:12 |
+---------+---------+------------+-----------------------+---------------------+

what I am trying to do is to count the amount of the visits group by hours, I'd like to generate results like this way:

+------------+-----+-----+-----+-----+-----+-----+-------+------+
| locationId |  0  |  1  |  2  |  3  |  4  |  5  |  ...  |  23  |
+------------+-----+-----+-----+-----+-----+-----+-------+------+
|      12    |  15 |  12 |  34 |  67 |  78 |  89 |  ...  |  34  | 
+------------+-----+-----+-----+-----+-----+-----+-------+------+

How can I do that? I want to evaluate the variance of visits in the whole day.

Upvotes: 0

Views: 413

Answers (3)

Dhinakar
Dhinakar

Reputation: 4151

Try to use query like below one:

SELECT  location_id, sum(case when HOUR(time) = 0 then 1 else 0) as '0', 
        sum(case when HOUR(time) = 1 then 1 else 0) as '1',-- till 23
FROM table
GROUP BY location_id

Upvotes: 1

mconlin
mconlin

Reputation: 8733

Without testing I think this will work:

select locationid, hour(time) as hour, count(distinct userid) as usercnt
from table_1
group by locationid, hour;

It wont be transposed as you suggested but the data is the same

Upvotes: 1

Omar MEBARKI
Omar MEBARKI

Reputation: 647

This will guive you the number of visits hour by hour for each locationid

SELECT locationId, HOUR(time), COUNT(*)
FROM table
GROUP BY locationId, HOUR(time)

Upvotes: 1

Related Questions