Menu
I have an application that receives sms messages. What i want to do is make a statistic with mysql that will count meessages in a hour. For example in 7 am i received 10 sms messages, in 8 am i received 20 etc. My table has this columns ID, smsText, smsDate ... (others are not important). When i run this script:
SELECT HOUR(smsDate), COUNT(ID) FROM SMS_MESSAGES GROUP BY HOUR(smsDate)
it show how many messages i get in every hour. The problem is when i dont receive any message for example in 5pm, this statement does't return a row 17 with count 0, and i have a result like this:
Hour Count
...
15 10
16 5
18 2
...
, and what i want to get is this
Hour Count
...
15 10
16 5
17 0
18 2
...
I searched for a solution on the web, something with UNION but i don't understand how to implement that one in mine. Hope someone can help me.
952
It's all about the JOIN type. Using the suitable JOIN is crucial when you want to include zeros in the COUNT() aggregate. If you now use the aggregate function COUNT() , like in the code above, it will not count the NULL values and the result will be zero.
Using COUNT, without GROUP BY clause will return a total count of a number of rows present in the table. Adding GROUP BY, we can COUNT total occurrences for each unique value present in the column.
To count the number of rows, use the id column which stores unique values (in our example we use COUNT(id) ). Next, use the GROUP BY clause to group records according to columns (the GROUP BY category above). After using GROUP BY to filter records with aggregate functions like COUNT, use the HAVING clause.
The SQL COUNT() function returns the number of rows in a table satisfying the criteria specified in the WHERE clause. It sets the number of rows or non NULL column values. COUNT() returns 0 if there were no matching rows.
You could create a table with all hours and join the tables:
CREATE TABLE IF NOT EXISTS `hours` (
`hour` int(11) NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
INSERT INTO `hours` (`hour`) VALUES (0), (1), (2), (3), (4), (5), (6), (7), (8), (9), (10), (11), (12), (13), (14), (15), (16), (17), (18), (19), (20), (21), (22), (23);
SELECT hours.hour, count( SMS_MESSAGES.ID )
FROM hours
LEFT JOIN SMS_MESSAGES ON ( hours.hour = HOUR( SMS_MESSAGES.smsDate ) )
GROUP BY 1
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With
