Group by month and year in MySQL

Question

Given a table with a timestamp on each row  how would you format the query to fit into this specific json object format   I am trying to organize a json object into years   months   json to base the query off        2009    August   July   September       2010    January    February    October       Here is the query I have so far -   SELECT     MONTHNAME t summaryDateTime  as month  YEAR t summaryDateTime  as year FROM     trading summary t  GROUP BY MONTH t summaryDateTime  DESC     The query is breaking down because it is  predictably  lumping together the different years

User · Answer

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

User · Answer

GROUP BY DATE FORMAT summaryDateTime   Y- m

User · Answer

Use  GROUP BY year  month DESC     Instead of   GROUP BY MONTH t summaryDateTime  DESC

User · Answer

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

User · Answer

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

User · Answer

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

User · Answer

GROUP BY YEAR t summaryDateTime   MONTH t summaryDateTime    is what you want

User · Answer

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

User · Answer

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

User · Answer

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

User · Answer

use EXTRACT function like this  mysql gt  SELECT EXTRACT YEAR FROM  2009-07-02           - gt  2009

User · Answer

This is how I do it   GROUP BY  EXTRACT YEAR MONTH FROM t summaryDateTime

[sql] Group by month and year in MySQL

Examples related to sql

Examples related to mysql

Examples related to group-by

Examples related to date