How to get sum null values as zero while using group by

Question

I have mysql query

SELECT date, COALESCE(SUM(events),0) AS hotel_c
FROM event_cohort_report
WHERE date >= '2015-08-05' AND campaign='Pointific_Incent' AND country='IN'
GROUP BY date

when i run this query i get this result

date       hotel_c
2015-08-07 5411
2015-08-08 4602
2015-08-09 5151
2015-08-10 183
2015-08-11 1

But i want for all date sum with zero if its not present in the table something like this.

date       hotel_c
2015-08-05 0
2015-08-06 0
2015-08-07 5411
2015-08-08 4602
2015-08-09 5151
2015-08-10 183
2015-08-11 1

Do rows with those dates exist in the table at all? – pete the pagan-gerbil Aug 14 '15 at 08:07 — pete the pagan-gerbil, Aug 14 '15 at 08:07

score 0 · Answer 1 · answered Aug 14 '15 at 09:12

0

The code that you are using is correct for getting value for null records. But I don't know the table and records in that as you have not mentioned. I have a working modal on this link

You can also use IFNULL(SUM(column),0) for replacing null with 0

answered Aug 14 '15 at 09:12

Senthil Kumar J

208
2
13

To satisfy OP requirements your SQL Fiddle example should show zero counts for 1st, 2nd & 3rd of August as the start date for your query was 1st August. – PaulF Aug 14 '15 at 10:04
that's fetched from the records of the table. I do not have records for those date – Senthil Kumar J Aug 14 '15 at 11:55
That's the point of the original question - see the answer to the first comment. – PaulF Aug 14 '15 at 12:10

How to get sum null values as zero while using group by

1 Answers1