[Solved] Mysql Query [0002] [closed]

by


Assuming you want a figure of 0 for any month and / or city which doesn’t have any sales, but where there are sales for other cities for that month then something like this:-

Cross join a pair of subselects, one to get a list of the months used and one to get a list of the cities, then join those against the records to get the amounts for that month / city, and sum those amounts up:-

SELECT Sub1.YearMonth, Sub2.city, SUM(sales.amount)
FROM (SELECT DISTINCT EXTRACT(YEAR_MONTH FROM `date`) AS YearMonth
FROM sales) Sub1
CROSS JOIN (SELECT DISTINCT city FROM users) Sub2
LEFT OUTER JOIN sales ON Sub1.YearMonth = EXTRACT(YEAR_MONTH FROM sales.`date`) 
LEFT OUTER JOIN users ON sales.user_id = users.id AND Sub2.city = sales.city
GROUP BY Sub1.YearMonth, Sub2.city
ORDER BY Sub1.YearMonth, Sub2.city

Down side of this is that if you have a month where nothing was sold to anybody then this month will not appear at all. To get around this you would need to change the subselect for the months to instead take a start date and add a range of numbers to it to get each month.

An example of generating a range is as follows:-

SELECT Sub1.YearMonth, Sub2.city, SUM(sales.amount)
FROM (SELECT EXTRACT(YEAR_MONTH FROM DATE_ADD('2009-01-01', INTERVAL a.i*100+b.i*10+c.i MONTH)) AS aMonth
FROM (SELECT 0 AS i UNION SELECT 1 UNION SELECT 2 UNION SELECT 3 UNION SELECT 4 UNION SELECT 5 UNION SELECT 6 UNION SELECT 7 UNION SELECT 8 UNION SELECT 9) a,
(SELECT 0 AS i UNION SELECT 1 UNION SELECT 2 UNION SELECT 3 UNION SELECT 4 UNION SELECT 5 UNION SELECT 6 UNION SELECT 7 UNION SELECT 8 UNION SELECT 9) b,
(SELECT 0 AS i UNION SELECT 1 UNION SELECT 2 UNION SELECT 3 UNION SELECT 4 UNION SELECT 5 UNION SELECT 6 UNION SELECT 7 UNION SELECT 8 UNION SELECT 9) c
WHERE DATE_ADD('2009-01-01', INTERVAL a.i*100+b.i*10+c.i MONTH) <=  '2014-12-01') Sub1
CROSS JOIN (SELECT DISTINCT city FROM users) Sub2
LEFT OUTER JOIN sales ON Sub1.YearMonth = EXTRACT(YEAR_MONTH FROM sales.`date`) 
LEFT OUTER JOIN users ON sales.user_id = users.id AND Sub2.city = sales.city
GROUP BY Sub1.YearMonth, Sub2.city
ORDER BY Sub1.YearMonth, Sub2.city

This example is giving every month between 2009-01-01 and 2014-12-01 inclusive, then getting all the sales by city for that range. It will cope with a range of up to 1000 months.

solved Mysql Query [0002] [closed]