Populate PHP sales table from MySQL (inc. empty months)
I have this query, which returns sales by month:
SELECT YEAR(txn_date) as 'year', MONTHNAME(txn_date) as 'month',
SUM(grand_total) as 'sales'
WHERE YEAR(txn_date) BETWEEN '$last_year' AND '$this_year'
GROUP BY month
What's a good way of populating a table showing sales for the last two years, by month, including months that have no sales? Normally I like to get SQL to do as much as possible, but in this case I don't think it can help me as there's no month table or anything like that.
Am I going to have to do some crude For Loop that counts to 12 and searches through the array for matches on month and year? I feel I'm missing some obvious, elegant way of doing this...
You can easily build in-line tables for months / years using CROSS JOIN:
SELECT y.`year` AS 'year', x.`month` AS 'month', SUM(grand_total) AS 'sales'
SELECT 1 AS `month` 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 UNION ALL
SELECT 10 UNION ALL SELECT 11 UNION ALL SELECT 12 ) AS x
CROSS JOIN (
SELECT 2015 AS ` year` UNION ALL SELECT 2016) AS y
LEFT JOIN transactions AS z ON x.`month` = MONTH(txn_date) AND
y.`year` = YEAR(txn_date)
GROUP BY x.`month`, y.`year`