How to get 0 records if a particular month has no records?

Question

I have query, which shows last 12 month record, it is working fine for me, but i want to show that month also which have no records, if there is no records for the particular month then i want to show 0 records for that month, here i have added my query for that

SELECT `amount`, CONVERT_TZ(created, "+00:00", "-05:00") as created, 
       MONTHNAME(CONVERT_TZ(created, "+00:00", "-05:00")) as month, 
       `sponsorion_fees`, `processor_fees`, `amount_after_fees`, 
       SUM(amount_after_fees) as total FROM `transaction` 
WHERE `record_owner_user_id` = '50' AND `is_one_time_purchase` = 'Y' 
       AND CONVERT_TZ(created,"+00:00","-05:00") <= "2018-11-26 07:08:24"
       and CONVERT_TZ(created,"+00:00","-05:00") >= 
       Date_add("2018-11-26 07:08:24",interval - 12 month)
GROUP BY `month` ORDER BY `id` ASC

Can anyone please help me how can i resolve this issue ?

You could perhaps RIGHT JOIN to a sub-query with the 12 months. Then group on the month from the sub-query. — LukStorms, Nov 26 '18 at 12:36

Saurabh Mistry · Answer 1 · 2018-11-26T12:22:52.387

try IFNULL :

SELECT `amount`, CONVERT_TZ(created, "+00:00", "-05:00") as created,
                 MONTHNAME(CONVERT_TZ(created, "+00:00", "-05:00")) as month,
                 `sponsorion_fees`, `processor_fees`, `amount_after_fees`, 
                 SUM(IFNULL(amount_after_fees, 0)) as total FROM `transaction`
WHERE `record_owner_user_id` = '50' 
                     AND `is_one_time_purchase` = 'Y' 
                     AND CONVERT_TZ(created,"+00:00","-05:00") <= "2018-11-26 07:08:24" 
                     and CONVERT_TZ(created,"+00:00","-05:00") >= Date_add("2018-11-26 07:08:24",interval - 12 month)
GROUP BY `month` 
ORDER BY `id` ASC

also try COALESCE :

SELECT `amount`, CONVERT_TZ(created, "+00:00", "-05:00") as created,
                     MONTHNAME(CONVERT_TZ(created, "+00:00", "-05:00")) as month,
                     `sponsorion_fees`, `processor_fees`, `amount_after_fees`, 
                     COALESCE(SUM(amount_after_fees),0) as total FROM `transaction`
    WHERE `record_owner_user_id` = '50' 
                         AND `is_one_time_purchase` = 'Y' 
                         AND CONVERT_TZ(created,"+00:00","-05:00") <= "2018-11-26 07:08:24" 
                         and CONVERT_TZ(created,"+00:00","-05:00") >= Date_add("2018-11-26 07:08:24",interval - 12 month)
    GROUP BY `month` 
    ORDER BY `id` ASC

What happen if last month have no record, then in group by it will not take that month so it will not give me record — Nikul Panchal, Nov 26 '18 at 12:22
@NikulPanchal , you can try this :https://stackoverflow.com/questions/32284341/group-by-month-return-0-if-no-record-found — Saurabh Mistry, Nov 26 '18 at 12:27
This doesn't work if the table contains no transactions in a particular month, unfortunately. — O. Jones, Nov 26 '18 at 12:29

How to get 0 records if a particular month has no records?

1 Answers1