SQL aggregate with the previous result

user_7540407 0
Viewed 23

I have the following table

Year1 Year2 Value
2014 2014 1
2014 2014 2
2014 2015 3
2014 2016 4
2015 2015 5
2015 2016 6
2015 2017 7
2015 2017 8

and I need to write a SQL query to give me this:

Year1 Year2 Value
2014 2014 3
2014 2015 6
2014 2016 10
2015 2015 5
2015 2016 11
2015 2017 26

The group by can help with grouping year1 and year2 but my problem is the cumulative result from the past aggregation.

sql-server sql
1 Answers

Aggregate in your groups first, and then use a windowed aggregate:

WITH CTE AS(
    SELECT Year1,
           Year2,
           SUM([Value]) AS [Value]
    FROM dbo.YourTable
    GROUP BY Year1,
             Year2)
SELECT Year1,
       Year2,
       SUM([Value]) OVER (PARTITION BY Year1 ORDER BY Year2) AS [Value]
FROM CTE;
user_20299830
Related