Make zero instead of null if there are no records in for mysql SUM query with group by

Question

I writes a query to get the result like sum of the amount for each product group by date.
When getting the output some products does not have corresponding amount for the date.that dates are not listing in the output So i need every date to display and if amount is there it need to show the amount otherwise 0.

I tried using IFNULL and COALESCE but getting same output.

My OUTPUT 

 date         amount
 2019-05-16   499
 2019-05-17   1998
 2019-05-18   999
 2019-05-19   999

Needed output should look like this

 date         amount
 2019-05-16   499
 2019-05-17   1998
 2019-05-18   999
 2019-05-19   999
 2019-05-20   0

query for getting data from mysql

$query = "select date, SUM(amount) as amount from product_details where date between '2019-05-16 00:00:00' and '2019-05-20 23:59:59' and prodid = 1 group by date";

Is there any way to make appear the date which does not have amount?

But equally, you can handle this perfectly well in your application code. — Strawberry, May 20 '19 at 13:45
Try my answer. If it doesn't work, replicate it here and I'll help — SoWhat, May 20 '19 at 14:03

score 0 · Answer 1 · answered May 20 '19 at 14:02

Try this. First we generate a record set of all dates between curdate() and first jan. Change curdate() to end date and 1 jan to start date. Then we outer join this with your table and use it to group by date.

  With cal as  (SELECT a.Days 
    FROM (
        SELECT curdate() - INTERVAL (a.a + (10 * b.a) + (100 * c.a)) DAY AS Days
        FROM       (SELECT 0 AS a UNION ALL SELECT 1 UNION ALL SELECT 2 UNION ALL SELECT 3 UNION ALL SELECT 4 UNION ALL SELECT 5 UNION ALL SELECT 6 UNION ALL SELECT 7 UNION ALL SELECT 8 UNION ALL SELECT 9) AS a
        CROSS JOIN (SELECT 0 AS a UNION ALL SELECT 1 UNION ALL SELECT 2 UNION ALL SELECT 3 UNION ALL SELECT 4 UNION ALL SELECT 5 UNION ALL SELECT 6 UNION ALL SELECT 7 UNION ALL SELECT 8 UNION ALL SELECT 9) AS b
        CROSS JOIN (SELECT 0 AS a UNION ALL SELECT 1 UNION ALL SELECT 2 UNION ALL SELECT 3 UNION ALL SELECT 4 UNION ALL SELECT 5 UNION ALL SELECT 6 UNION ALL SELECT 7 UNION ALL SELECT 8 UNION ALL SELECT 9) AS c
    ) a
    WHERE a.Days >= '2019-01-01' and a.Days<=curdate()) 
    SELECT cal.Days as date, sum(p.amount)  as amount FROM cal LEFT JOIN product_details p  on cal.Days=p.date and p.prodid=1 group by cal.Days

Make zero instead of null if there are no records in for mysql SUM query with group by

1 Answers1