Group by month and year in MySQL
This is for the derived table to be used with the main query – which will use MAX(firstcount) etc. THe main query will also have to group by the year and month fields.
(SELECT YEAR(date1) AS yr, MONTH(date1) AS m, COUNT(date1) AS firstcount, 0 AS secondcount, 0 AS firstsum
GROUP BY YEAR(date1), MONTH(date1)
SELECT YEAR(date2) AS yr, MONTH(date2) AS m, 0 AS firstcount, COUNT(date2) AS secondcount, SUM(price) AS firstsum
GROUP BY YEAR(date2), MONTH(date2)) AS derivedtable