[Solved] Cummulative SUM based on columns

by


No need to make thinks more complicated than they need to be…

IF OBJECT_ID('tempdb..#TestData', 'U') IS NOT NULL 
DROP TABLE #TestData;

CREATE TABLE #TestData (
    ID INT NOT NULL,
    [Year] INT NOT NULL 
    );
INSERT #TestData (ID, Year) VALUES
    (1, 2010), (1, 2010), (2, 2010), (2, 2011), 
    (3, 2012), (4, 2013), (5, 2014), (5, 2014),
    (5, 2014), (5, 2015), (5, 2016);

--=======================================

SELECT
    tdg.ID,
    tdg.Year,
    RunningCount = SUM(tdg.Cnt) OVER (PARTITION BY tdg.ID ORDER BY tdg.Year ROWS UNBOUNDED PRECEDING)
FROM (   
    SELECT td.ID, td.Year, Cnt = COUNT(1)
    FROM #TestData td
    GROUP BY td.ID, td.Year
    ) tdg;

Results…

ID          Year        RunningCount
----------- ----------- ------------
1           2010        2
2           2010        1
2           2011        2
3           2012        1
4           2013        1
5           2014        3
5           2015        4
5           2016        5

solved Cummulative SUM based on columns