The Fine Print: The following comments are owned by whoever posted them. We are not responsible for them in any way.
(1)
(Score: 0) by Anonymous Coward on Thursday July 09 2020, @10:02AM
(1 child)
by Anonymous Coward
on Thursday July 09 2020, @10:02AM (#31616)
The inner query is effectively a list of <y,m,v> tuples, v=v(y,m) for year=y in range 2014..., month=m. What I was after is a list of <m, v(y=2014,m), v(y=2015,m), ..., v(y=2020,m)> This works by summing lots of things that are 0 almost everywhere:
SELECT MONTHNAME(rawdate), m, SUM(if(y=2014,v,0)) as v2014, SUM(if(y=2015,v,0)) as v2015, SUM(if(y=2016,v,0)) as v2016, SUM(if(y=2017,v,0)) as v2017, SUM(if(y=2018,v,0)) as v2018, SUM(if(y=2019,v,0)) as v2019, SUM(if(y=2020,v,0)) as v2020 FROM (SELECT ... AS rawdate, YEAR(...) AS y, MONTH(...) AS m, ROUND((SUM(...)),2) AS v FROM ymv_table GROUP BY y,m ) as ymv GROUP BY m ORDER BY m
(Score: 2) by martyb on Thursday July 09 2020, @10:18AM
(Score: 0) by Anonymous Coward on Thursday July 09 2020, @10:02AM (1 child)
What I was after is a list of <m, v(y=2014,m), v(y=2015,m), ..., v(y=2020,m)>
This works by summing lots of things that are 0 almost everywhere:
SELECT
MONTHNAME(rawdate),
m,
SUM(if(y=2014,v,0)) as v2014,
SUM(if(y=2015,v,0)) as v2015,
SUM(if(y=2016,v,0)) as v2016,
SUM(if(y=2017,v,0)) as v2017,
SUM(if(y=2018,v,0)) as v2018,
SUM(if(y=2019,v,0)) as v2019,
SUM(if(y=2020,v,0)) as v2020
FROM
(SELECT ... AS rawdate,
YEAR(...) AS y,
MONTH(...) AS m,
ROUND((SUM(...)),2) AS v
FROM ymv_table
GROUP BY y,m
) as ymv
GROUP BY m
ORDER BY m
(Score: 2) by martyb on Thursday July 09 2020, @10:18AM
You know what the actual data looks like; nobody else does. That makes this rather difficult. It would help if you could provided the following: