how to calculate YTD (Year To Date) value in Oracle table

user_2575502 0
Viewed 5423

There is a table and its data looks like this: 

year month v1 v2    
2017 1     2  3
2017 2     4  5
...  ..   ..  ..
2017 12    9  3    
2018 1     1  3
2018 2     6  2
...  ..   ..  ..
2018 12    2  2

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 ?

oracle sql
3 Answers

You could use analytic funtion SUM() OVER()

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;
user_38444580

Not sure it will work in Oracle 10g and earlier. Try to add this field:
SUM(v1) OVER (PARTITION BY year ORDER BY month)

user_106005840 
select 
    empid, 
    DOJ, 
    emp_salary,
    sum(emp_salary) over (
        partition by Extract(year from DOJ) order by DOJ asc
    ) as YTD
from emp;
user_148714970
Related