## Dev.SN ♥ developers

### Code Playground #1

Paste your code into a comment on this story.
This discussion has been archived. No new comments can be posted.
Display Options Breakthrough
The Fine Print: The following comments are owned by whoever posted them. We are not responsible for them in any way.
(1)
• #### turn <y,m,v> triplets into <m,v_y1,...v_yn>tuplesturn <y,m,v> triplets into <m,v_y1,...v_yn>tuples(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
• #### and the data looks like?(Score: 2) by martyb on Thursday July 09 2020, @10:18AM

by martyb (76) on Thursday July 09 2020, @10:18AM (#31617) Journal

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:

1. SELECT * FROM year;
2. SELECT * FROM month;
3. SELECT * FROM ymv_table;
4. the result of the provided query
(1)