SQL output unique visitors for each hour

Question

I'm pretty novice when it comes to SQL, and was faced with a problem last week from work. I was able to over come it with RoR queries and reformat the data for my need(messy D: ); however, I really would like to know the sql's solution to it, if there's any

I have a MySQL database looking like this

| id | unique_visitors |      time_period    |
| 1  |              16 | 2013-07-01 00:00:00 |
| 2  |              20 | 2013-07-01 01:00:00 |
| 3  |               2 | 2013-07-01 02:00:00 |

The final output I need looks like this

|    date    | 00 | 01 | 02 | 03 | 04 | 05 | 06 | 07 | 08 | 09 |.... | 22 | 23|
| 2013-07-01 | 16 | 20 |  2 | 32 | .........

Each row would display the date, and unique visitors at each hour.

I'm not sure if this is possible with sql query, and really would like to take this opportunities to learn something.

Thank You SQL Guru!

hint: you could use a CASE statement with a sum and group by... — Mitch Wheat, Jul 27 '13 at 03:38
this is a pivot query, read this thread: http://stackoverflow.com/questions/7674786/mysql-pivot-table — krokodilko, Jul 27 '13 at 05:03

Jeff · Accepted Answer · 2013-07-27T14:47:59.383

It gets complicated with CASE statements, but here you are. I felt like a challenge before going to bed

table stats(id,unique_visitors,time_period)

select DATE_FORMAT(time_period ,'%Y-%m-%d') as date, 
sum(CASE WHEN EXTRACT(hour from time_period)  =  00 THEN unique_visitors ELSE  0 END) as '00', 
sum(CASE WHEN EXTRACT(hour from time_period)  =  01 THEN unique_visitors ELSE  0 END) as '01', 
sum(CASE WHEN EXTRACT(hour from time_period)  =  02 THEN unique_visitors ELSE  0 END) as '02',  
sum(CASE WHEN EXTRACT(hour from time_period)  =  03 THEN unique_visitors ELSE  0 END) as '03',  
sum(CASE WHEN EXTRACT(hour from time_period)  =  04 THEN unique_visitors ELSE  0 END) as '04',  
sum(CASE WHEN EXTRACT(hour from time_period)  =  05 THEN unique_visitors ELSE  0 END) as '05',  
sum(CASE WHEN EXTRACT(hour from time_period)  =  06 THEN unique_visitors ELSE  0 END) as '06',  
sum(CASE WHEN EXTRACT(hour from time_period)  =  07 THEN unique_visitors ELSE  0 END) as '07',  
sum(CASE WHEN EXTRACT(hour from time_period)  =  08 THEN unique_visitors ELSE  0 END) as '08',  
sum(CASE WHEN EXTRACT(hour from time_period)  =  09 THEN unique_visitors ELSE  0 END) as '09',  
sum(CASE WHEN EXTRACT(hour from time_period)  =  10 THEN unique_visitors ELSE  0 END) as '10',  
sum(CASE WHEN EXTRACT(hour from time_period)  =  11 THEN unique_visitors ELSE  0 END) as '11',  
sum(CASE WHEN EXTRACT(hour from time_period)  =  12 THEN unique_visitors ELSE  0 END) as '12',  
sum(CASE WHEN EXTRACT(hour from time_period)  =  13 THEN unique_visitors ELSE  0 END) as '13',  
sum(CASE WHEN EXTRACT(hour from time_period)  =  14 THEN unique_visitors ELSE  0 END) as '14',  
sum(CASE WHEN EXTRACT(hour from time_period)  =  15 THEN unique_visitors ELSE  0 END) as '15',  
sum(CASE WHEN EXTRACT(hour from time_period)  =  16 THEN unique_visitors ELSE  0 END) as '16',  
sum(CASE WHEN EXTRACT(hour from time_period)  =  17 THEN unique_visitors ELSE  0 END) as '17',   
sum(CASE WHEN EXTRACT(hour from time_period)  =  18 THEN unique_visitors ELSE  0 END) as '18',  
sum(CASE WHEN EXTRACT(hour from time_period)  =  19 THEN unique_visitors ELSE  0 END) as '19',  
sum(CASE WHEN EXTRACT(hour from time_period)  =  20 THEN unique_visitors ELSE  0 END) as '20',  
sum(CASE WHEN EXTRACT(hour from time_period)  =  21 THEN unique_visitors ELSE  0 END) as '21',  
sum(CASE WHEN EXTRACT(hour from time_period)  =  22 THEN unique_visitors ELSE  0 END) as '22',  
sum(CASE WHEN EXTRACT(hour from time_period)  =  23 THEN unique_visitors ELSE  0 END) as '23' 
from stats
group by date
order by date

outputs

      date  00  01  02  03  04  05  06  07  08  09  10  11  12  13 14  15  16  17  18  19  20  21  22  23
2013-07-01  22  16  2   0   0   0   0   0   0   0   0   0   0   0   0   0   0   0   0   0   0   0   0   0
2013-07-02  0   0   0   0   30  0   0   0   50  0   0   0   0   0   0   0   15  0   0   0   0   0   0   0

-Jeff Weiss

to make it bit less messy you can suggest him http://dev.mysql.com/doc/refman/5.1/en/adding-functions.html Functions? — Sumit Gupta, Jul 27 '13 at 05:23
Ah~!! Thank you very much Jeff~!It looks perfect, I shall ponder on your solution~! = ) — Mickey, Jul 27 '13 at 23:26
Glad I could help Mickey. Did this work for you? If so, plz give me an 'up' or 'solved' rating, I could use a point or two. later. — Jeff, Oct 11 '13 at 03:10

SQL output unique visitors for each hour

1 Answers1