Collectives™ on Stack Overflow
Find centralized, trusted content and collaborate around the technologies you use most.
Learn more about Collectives
Teams
Q&A for work
Connect and share knowledge within a single location that is structured and easy to search.
Learn more about Teams
the table has year, month, v1, v2 totally 4 fields, now I want a SQL to calculate YTD value for each row, the query result looks like this:
year month v1 v2 v1_YTD v2_YTD
2017 1 2 3 2 3
2017 2 4 5 6 8
... .. .. .. .. ..
2017 12 9 3 m1 n1
2018 1 1 3 1 3
2018 2 6 2 7 5
... .. .. .. .. ..
2018 12 2 2 m2 n2
YTD value, for example, calculate for February, then the YTD value = January + February, if the month is December, the YTD value should be the value for January + February + March +...+ December.
YTD value should not cross year.
Is there a SQL can achieve this purpose ?
SELECT year, month, v1, v2,
SUM(v1) OVER (PARTITION BY year ORDER BY month) AS v1_ytd,
SUM(v2) OVER (PARTITION BY year ORDER BY month) AS v2_ytd
FROM table_name
ORDER BY year, month;
Thanks for contributing an answer to Stack Overflow!
- Please be sure to answer the question. Provide details and share your research!
But avoid …
- Asking for help, clarification, or responding to other answers.
- Making statements based on opinion; back them up with references or personal experience.
To learn more, see our tips on writing great answers.
