Cumulative sum with group by

0

Please login or register to vote for this query.

(click on this box to dismiss)

Reverse Engineering Meta

Q&A about the site for researchers and developers who explore the principles of a system through analysis of its structure, function, and operation

DECLARE @t TABLE(ColumnA varchar(50), ColumnB INT);

INSERT INTO @t VALUES
('firstname', 10),
('lastname', 10),
('dob', 10),
('address', 10),
('status', 10);

;WITH cteRanked AS
(
   SELECT ColumnA, ColumnB, ROW_NUMBER() OVER(ORDER BY ColumnB) rownum
   FROM @t
),

last as 
(
SELECT ColumnA, 
(SELECT SUM(ColumnB) FROM cteRanked c2 WHERE c2.rownum <= c1.rownum) as total
, ColumnB
FROM cteRanked c1
)

select ColumnA, Total as first, Total - ColumnB + 1 as last from last;

Enter Parameters

Options:
Switch sites:
loading Hold tight while we fetch your results