GROUP BY DATE_FORMAT(summaryDateTime,'%Y-%m') 

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); 

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; 

SELECT MONTHNAME(t.summaryDateTime) as month, YEAR(t.summaryDateTime) as year FROM trading_summary t GROUP BY YEAR(t.summaryDateTime) DESC, MONTH(t.summaryDateTime) DESC 

Should use DESC for both YEAR and Month to get correct order.

You must do something like this

SELECT onDay, id, sum(pxLow)/count(*),sum(pxLow),count(`*`), CONCAT(YEAR(onDay),"-",MONTH(onDay)) as sdate FROM ... where stockParent_id =16120 group by sdate order by onDay 

This is how I do it:

GROUP BY EXTRACT(YEAR_MONTH FROM t.summaryDateTime); 

use EXTRACT function like this

mysql> SELECT EXTRACT(YEAR FROM '2009-07-02'); -> 2009 

You cal also do this

SELECT SUM(amnt) `value`,DATE_FORMAT(dtrg,'%m-%y') AS label FROM rentpay GROUP BY YEAR(dtrg) DESC, MONTH(dtrg) DESC LIMIT 12 

to order by year and month. Lets say you want to order from this year and this month all the way back to 12 month

You are grouping by month only, you have to add YEAR() to the group by

SELECT YEAR(t.summaryDateTime) as yr, GROUP_CONCAT(MONTHNAME(t.summaryDateTime)) AS month FROM trading_summary t GROUP BY yr 

Still you would need to process it in external script to get exactly the structure you're looking for.

For example use PHP's explode to create an array from list of month names and then use json_encode()

As this data is being pulled for a trade summary by month, I had to do the same thing and would just like to add the code I use. The data I have is saved in the database as a string so your query may be simpler. Either way, as a trader, this is in essence what most people would be looking for:

select DATE(DATE_FORMAT(STR_TO_DATE(closeDate, '%Y-%m-%d'), '%Y-%m-01')) AS month_beginning, COUNT(*) AS trades, TRUNCATE(sum(profitLoss)/count(*),2) as 'avgProfit', TRUNCATE(sum(percentGain)/count(*),2) as 'avgPercent', sum(profitLoss) as 'gi', sum(profitLoss > 0)/count(*) AS winPercent, sum(profitLoss < 0)/count(*) as 'lossPercent', max(profitLoss) as bigWinner, min(profitLoss) as bigLoser from tradeHistory group by month_beginning order by month_beginning DESC 

However, it will skip months that are missing in your data. So if there is no data for Jan, there won't be a row.

I would like add difference between group by year(datetime),month(datetime) and groupb by extract(year_month from datetime). here is the query i tried with these two cases.

select year(datetimecol) as Year,monthname(datetimecol) as Month from table
group by year(datetimecol) and month(datetimecol) order by year(datetimecol) desc;

result:

Year, Month

2020, May

select year( datetimecol) as Year,monthname(datetimecol) as Month from table GROUP BY EXTRACT(YEAR_MONTH FROM datetimecol) order by year(datetimecol) desc,month(datetimecol) asc;

result:

Year, Month

2021, January 2021, February 2021, March 2021, April 2021, May 2020, May 2020, June 2020, July 2020, August 2020, September 2020, October 2020, November 2020, December

(this is the result i need)

My observation 1.when i am using with group by year(datetimecol),month(datetimecol) , not giving desired result ..might be because of datetime feild... 2.when i tired second query with GROUP BY EXTRACT(YEAR_MONTH FROM datetimecol) order by year(datetimecol)...its working absolutely fine.

in conclusion, for getting months by year wise use the following query.

select year( datetimecol) as Year,monthname(datetimecol) as Month from table GROUP BY EXTRACT(YEAR_MONTH FROM datetimecol) order by year(datetimecol) desc,month(datetimecol) asc;