I have a database with a chat history that has a datime field, author of the messsage and the message.
I wan´t to make a query that returns a table that, correlates the weekday with hours of day, counting how many messages was sent.
Something like this:
+-------+--------+--------+----------+------------+---------+--------+---------+
| HOURS | SUNDAY | MONDAY | THURSDAY | WEDNESDAY | TUESDAY | FRIDAY | SABBATH |
+-------+--------+--------+----------+------------+---------+--------+---------+
| 00 | 5 | 25 | 44 | 46 | 23 | 12 | 42 |
| 01 | 4 | 545 | 35 | 76 | 65 | 15 | 354 |
| 02 | 36 | 654 | 365 | 76 | 65 | 42 | 44 |
| 03 | 3 | 578 | 678 | 83 | 21 | 32 | 88 |
| 04 | 8 | 78 | 678 | 92 | 878 | 34 | 98 |
| 05 | 87 | 987 | 89 | 26 | 09 | 67 | 75 |
| 06 | 54 | 65 | 98 | 23 | 65 | 54 | 55 |
| 07 | 98 | 64 | 07 | 43 | 656 | 42 | 47 |
| 08 | 98 | 56 | 89 | 65 | 76 | 43 | 62 |
| 09 | 98 | 768 | 678 | 75 | 76 | 26 | 763 |
| 10 | 3 | 56 | 54 | 87 | 87 | 76 | 45 |
| 11 | 39 | 567 | 236 | 76 | 897 | 12 | 62 |
| 12 | 987 | 765 | 342 | 87 | 987 | 76 | 67 |
| 13 | 45 | 456 | 456 | 97 | 43 | 69 | 876 |
| 14 | 235 | 87 | 44 | 24 | 2865 | 98 | 86 |
| 15 | 543 | 987 | 44 | 45 | 367 | 87 | 87 |
| 16 | 897 | 87 | 64 | 37 | 898 | 67 | 98 |
| 17 | 678 | 67 | 76 | 54 | 245 | 56 | 87 |
| 18 | 766 | 89 | 99 | 54 | 689 | 35 | 24 |
| 19 | 98 | 98 | 987 | 55 | 467 | 76 | 25 |
| 20 | 45 | 73 | 789 | 62 | 98 | 35 | 177 |
| 21 | 876 | 897 | 35 | 289 | 56 | 76 | 57 |
| 22 | 876 | 978 | 546 | 35 | 874 | 24 | 543 |
| 23 | 76 | 945 | 456 | 64 | 87 | 64 | 76 |
+-------+--------+--------+----------+------------+---------+--------+---------+
It is possible to make that?
I have tried with this code, I think is a start, but I realy don´t know if I'm going in the right direction.
select
hour(sendtime) as 'HOUR',
dayofweek(sendtime) = 1 as 'Sunday',
dayofweek(sendtime) = 2 as 'Monday',
dayofweek(sendtime) = 3 as 'Tuesday',
dayofweek(sendtime) = 4 as 'Wednesday',
dayofweek(sendtime) = 5 as 'Thursday',
dayofweek(sendtime) = 6 as 'Friday',
dayofweek(sendtime) = 7 as 'Sabbath',
count(*) AS 'COUNT'
from chat
group by HOUR(sendtime)
I want this data to make a visualization usiging D3.js, something that looks like this:
http://www.imagesup.net/?di=16141790283414
Thanks.
You are very close, you just need aggregation functions:
select hour(sendtime) as `HOUR`,
sum(dayofweek(sendtime) = 1) as Sunday,
sum(dayofweek(sendtime) = 2) as Monday,
sum(dayofweek(sendtime) = 3) as Tuesday,
sum(dayofweek(sendtime) = 4) as Wednesday,
sum(dayofweek(sendtime) = 5) as Thursday,
sum(dayofweek(sendtime) = 6) as Friday,
sum(dayofweek(sendtime) = 7) as Sabbath,
count(*) AS 'COUNT'
from chat
group by HOUR(sendtime)
order by `hour`;
Note that you do not need single quotes around column aliases. You should only use single quotes for string and date constants.