Logo Questions Linux Laravel Mysql Ubuntu Git Menu
 

including missing (zero-count) rows when using GROUP BY

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.

like image 952
AdrianES Avatar asked Jun 19 '14 11:06

AdrianES


People also ask

How do you include zeros in a count aggregate?

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.

Can we use group by without count?

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.

How do I count rows in a group by?

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.

Does count in SQL count 0?

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.


1 Answers

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 
like image 121
hellcode Avatar answered Nov 01 '22 17:11

hellcode