SQL COUNT(*) return 0 IFNULL with WHERE clauses and GROUP_BY on same table

Question

The result should return a row with the date even if the count is null.

SELECT DATE_FORMAT(created_date, '%Y-%m') AS date, COUNT(*) AS total
FROM mytable
WHERE created_date BETWEEN :startDate AND :endDate 
AND condition = :condition
GROUP BY DATE_FORMAT(created_date, '%Y-%m')
ORDER BY created_date ASC

I saw that i should use LEFT JOIN and IFNULL, but I tried with no good result.

Desired result :

array(1) {
  [0]=> {
    ["date"]=>
    string(7) "2016-01"
    ["total"]=>
    string(1) "0"
  }
  [1]=> {
    ["date"]=>
    string(7) "2016-02"
    ["total"]=>
    string(4) "9492"
  }
  [2]=> {
    ["date"]=>
    string(7) "2016-03"
    ["total"]=>
    string(1) "0"
  }
}

PS : I'm using MySQL

Which `DBMS` you are using. Tired of writing this statement :/ — Pரதீப், Oct 11 '16 at 16:27
When you say the result should return a row, is that the expected or desired result? — Dan Bracuk, Oct 11 '16 at 16:28
@mchev - Actually you should tag. I have did it for you. Keep this in mind for future questions — Pரதீப், Oct 11 '16 at 16:31

Pரதீப் · Accepted Answer · 2016-10-11T16:38:16.317

0

LEFT JOIN is correct but with what table you did Left join. You need a calendar table with List dates

You dont have to use IFNULL when there is no records found for a particular date then Count aggregate will return 0

SELECT Date_format(c.dates, '%Y-%m') AS date,
       Count(m.created_date)         AS total
FROM   Calendar_table c
       LEFT JOIN mytable m
              ON c.dates = Cast(m.created_date AS DATE)
WHERE  created_date BETWEEN :startDate AND :endDate
       AND condition = :condition
GROUP  BY Date_format(c.dates, '%Y-%m')
ORDER  BY c.dates ASC

For creating calendar table he is an article : Calendar Tables: An Invaluable Database Tool

edited Oct 11 '16 at 16:38

answered Oct 11 '16 at 16:29

Pரதீப்

91,748
19
131
172

For this case, i don't have a calendar table and can't create it. – mchev Oct 11 '16 at 16:35
@mchev - Check this question there is an option to generate dates with creating a table http://stackoverflow.com/questions/14105018/generating-a-series-of-dates – Pரதீப் Oct 11 '16 at 16:36
That's mean i should create a table and destroy it on every query?I think i'll take the php option to check dates who are not in the result. – mchev Oct 11 '16 at 16:51
@mchev - No, You can create a permanent table with list of dates whats wrong in that. That table will be very useful for reports – Pரதீப் Oct 11 '16 at 16:52
Ok, i'll check if i need this calendar for other parts. I don't want to create a big calendar table (need to go far far in time) for only one query. Thanks ;) – mchev Oct 11 '16 at 17:00
@mchev - There are options to generate dates without creating table. Check the answer I shared in previous comment. Am not a expert in `Mysql` else will share a example in my answer – Pரதீப் Oct 11 '16 at 17:06
Sure it's just one query now, but a calendar table is super helpful in a lot of queries. – Hart CO Oct 11 '16 at 17:34

SQL COUNT(*) return 0 IFNULL with WHERE clauses and GROUP_BY on same table

1 Answers1