Cumulative sum with group by

0

Please login or register to vote for this query.

(click on this box to dismiss)

Russian Language

Q&A for students, teachers, and linguists wanting to discuss the finer points of the Russian language

DECLARE @ta TABLE(idoggetto INT, visite INT, mese INT, anno INT);

INSERT INTO @ta VALUES
(100,   3,         8, 2017),
(100,   4,         9, 2017),
(100,   11,       10, 2017),
(100,   14,       11, 2017),
(100,   7,        12, 2017),
(100,   4,         1, 2018),
(100,   3,         2, 2018),
(100,   4,         3, 2018),
(101,   0,         8, 2017),
(101,   1,         9, 2017),
(101,   1,        10, 2017),
(101,   1,        11, 2017),
(101,   0,        12, 2017),
(101,   1,         1, 2018),
(101,   1,         2, 2018),
(101,   1,         3, 2018)
;

WITH cteRanked AS (
   SELECT idoggetto, visite, mese, anno, 
   ROW_NUMBER() OVER(PARTITION BY idoggetto ORDER BY anno, mese) rownum
   FROM @ta
   ) 

SELECT idoggetto, visite, 
       (SELECT SUM(visite) 
       FROM cteRanked c2 
       WHERE c2.rownum <= c1.rownum ) 
AS visite_cumulate, mese, anno
FROM cteRanked c1;

Enter Parameters

Options:
Switch to meta site
loading Hold tight while we fetch your results