I have the following query that give me paid, unpaid and total from a default period (actual month to 9 month ago). At this point all works fine.
My problem is when some periods have not results (I made GROUP BY months). For this particular records I need that return a 0 value for paid, unapid and total, and not ignore, because then I show this in a html table ordering by month and empty values produce that some columns not appear.
This is my query at this moment:
YEAR(PI.date_invoice) AS year,
MONTH(PI.date_invoice) AS month,
@unpaid := SUM(CASE
WHEN [url removed, login to view] = '1' THEN PI.amount_dolar
END) AS unpaid,
@paid := SUM(CASE
WHEN [url removed, login to view] = '2' THEN PI.amount_dolar
END) AS paid,
@total := SUM(CASE
WHEN [url removed, login to view] != '3' THEN PI.amount_dolar
END) AS total
FROM crm_provider_invoice PI
LEFT JOIN crm_provider P ON(PI.id_provider = P.id_provider)
WHERE DATE_FORMAT(PI.date_invoice, '%Y-%m-%d') BETWEEN DATE_SUB(LAST_DAY(SYSDATE()), INTERVAL 9 MONTH) AND DATE_SUB(LAST_DAY(SYSDATE()), INTERVAL 0 MONTH)
GROUP BY MONTH(PI.date_invoice)
ORDER BY year DESC, month DESC
What I need are records, month by month, with o without results. And in case that no result for one month, return with zero value.