Group by doesnt return "0"

Question

Im making a SELECT query for two tables.

Tables:

ec_events
ec_calendar

Select QUERY:

SELECT MONTH(`ec_calendar`.`Day`) AS Month ,COUNT(`ID_Event`) AS Count 
FROM `ec_events`,`ec_calendar` 
WHERE `ec_events`.`FK_Day`=`ec_calendar`.`ID_Day` AND YEAR(`ec_calendar`.`Day`) = 2015 
GROUP BY MONTH(`ec_calendar`.`Day`)

And the result:

The only problem here is May (5. month). On the result image you can see, that was ignored, because in ec_calendar is no entry for this month. Many times better will be, if 0 could be returned. So the table will loks so:

Is there any way to provide it?

what is `ec_calendar` table ? – Juan Carlos Oropeza Oct 16 '15 at 22:34 — Juan Carlos Oropeza, Oct 16 '15 at 22:34
The right one, with `ID_Day` and `Day` colums. – Bullman Oct 16 '15 at 22:37 — Bullman, Oct 16 '15 at 22:37

score 4 · Answer 1 · answered Oct 16 '15 at 22:33

4

Use a LEFT JOIN:

SELECT MONTH(`ec_calendar`.`Day`) AS Month ,COUNT(`ID_Event`) AS Count 
FROM `ec_events`
LEFT JOIN `ec_calendar` 
ON `ec_events`.`FK_Day`=`ec_calendar`.`ID_Day` 
WHERE YEAR(`ec_calendar`.`Day`) = 2015 
GROUP BY MONTH(`ec_calendar`.`Day`)

A LEFT JOIN will keep all records from your left table and fill in NULLs where it doesn't match to your right table.

answered Oct 16 '15 at 22:33

Mr. Llama

20,202
2
62
115

3

This wont work because your left side doesnt have a month `5`, you need an aditional `allMonths` table – Juan Carlos Oropeza Oct 16 '15 at 22:46
Right idea. Wrong order for the tables in the `from` clause. – Gordon Linoff Oct 16 '15 at 23:17
@GordonLinoff How changing the order will solve this? Right side doesnt have month 5 neither. In the sample is one but for `2016-05-28` – Juan Carlos Oropeza Oct 16 '15 at 23:28
@JuanCarlosOropeza . . . You want everything in `ec_calendar` in order to get days with zero events. – Gordon Linoff Oct 16 '15 at 23:52
@Gordon Linoff Now I may be wrong but to me looks like `ec_calendar` has some holes. And `MONTH(ec_calendar.Day)` not will bring all months? – Juan Carlos Oropeza Oct 16 '15 at 23:57
@JuanCarlosOropeza . . . It would seem odd to me that a table called `calendar` would be missing days. Admittedly, the sample with a handful of random dates doesn't provide much information on what the table really contains, so you might be right. – Gordon Linoff Oct 17 '15 at 20:44

score 1 · Accepted Answer · edited Jul 29 '20 at 18:25

1

You have to create a table allMonth with all months from 1..12, maybe you also will add year field if need it.

This sample will give you an idea of fill the table with date ranges

allMonth

 month_id
 year_id
 name

Query

SELECT AM.month_id AS Month, COUNT(`ID_Event`) AS Count 
FROM  allMonth  AM
LEFT JOIN `ec_calendar` EC
       ON AM.month_id = MONTH(EC.`Day`)
INNER JOIN `ec_events`  EV
       ON EV.`FK_Day`= EC.`ID_Day`
      AND YEAR(EC.`Day`) = 2015 
GROUP BY AM.month_id

Aditional TIPS:

Always try to use alias to improve reading
Learn how to use explicit join syntax. Aaron Bertrand did some writting about it

edited Jul 29 '20 at 18:25

Community

1
1

answered Oct 16 '15 at 22:38

Juan Carlos Oropeza

47,252
12
78
118

I dont think, that make another table is good solution. Here must be simplier way, but thanks for your time and answer. – Bullman Oct 16 '15 at 22:46
@Bullman, Sorry but there isnt. I have answer this same question multiple times. With users missing `years`. `months` or `days` In all cases the answer is create a table. And use the store procedure I include to fill the table. – Juan Carlos Oropeza Oct 16 '15 at 22:49
For example include a table with `100` years of days have only `36,500` so is very small, And believe me I did the math and isnt expensive neither on time executions. A table of months have only `1200 rows` – Juan Carlos Oropeza Oct 16 '15 at 22:52
@Bullman think about it. How would you group something when isnt there? – Juan Carlos Oropeza Oct 16 '15 at 22:56

Group by doesnt return "0"

2 Answers2