SQL aggregate with the previous result-CodePudding

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.

CodePudding user response：

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;