Group by month and year in MySQL
Solution 1:
GROUP BY YEAR(t.summaryDateTime), MONTH(t.summaryDateTime);
is what you want.
Solution 2:
GROUP BY DATE_FORMAT(summaryDateTime,'%Y-%m')
Solution 3:
I prefer
SELECT
MONTHNAME(t.summaryDateTime) as month, YEAR(t.summaryDateTime) as year
FROM
trading_summary t
GROUP BY EXTRACT(YEAR_MONTH FROM t.summaryDateTime);
Solution 4:
I know this is an old question, but the following should work if you don't need the month name at the DB level:
SELECT EXTRACT(YEAR_MONTH FROM summaryDateTime) summary_year_month
FROM trading_summary
GROUP BY summary_year_month;
See EXTRACT function docs
You will probably find this to be better performing.. and if you are building a JSON object in the application layer, you can do the formatting/ordering as you run through the results.
N.B. I wasn't aware you could add DESC to a GROUP BY clause in MySQL, perhaps you are missing an ORDER BY clause:
SELECT EXTRACT(YEAR_MONTH FROM summaryDateTime) summary_year_month
FROM trading_summary
GROUP BY summary_year_month
ORDER BY summary_year_month DESC;