daninthemix February 2016

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'
FROM transactions
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...

Answers


Giorgos Betsos February 2016

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'
FROM (
   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`

Demo here

Post Status

Asked in February 2016
Viewed 1,649 times
Voted 11
Answered 1 times

Search




Leave an answer