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.
This is how I do it:
GROUP BY EXTRACT(YEAR_MONTH FROM t.summaryDateTime);
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);
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.
use EXTRACT function like this
mysql> SELECT EXTRACT(YEAR FROM '2009-07-02');
-> 2009
You are grouping by month only, you have to add YEAR() to the group by
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
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
Use
GROUP BY year, month DESC";
Instead of
GROUP BY MONTH(t.summaryDateTime) DESC";
GROUP BY DATE_FORMAT(summaryDateTime,'%Y-%m')
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;
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 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()
Source: Stackoverflow.com