SQL Cumulative Count

Question

I have table with departments. I need to count how many people are within which dept. This is easily done by

SELECT DEPT,
       COUNT(*) as 'Total'
    FROM SR
    GROUP BY DEPT;

Now I need to also do cumulative count as below:

I have found some SQL to count running total, but not case like this one. Could you provide me some advice in this case, please?

Answer 1

Here's a way to do it with a CTE instead of a cursor:

WITH Base AS
(
    SELECT ROW_NUMBER() OVER (ORDER BY [Count] DESC) RowNum,
    [Dept],
    [Count]
    FROM SR
)
SELECT SR.Dept, SR.Count, SUM(SR2.[Count]) Total
FROM Base SR
INNER JOIN Base SR2
    ON SR2.RowNum <= SR.RowNum
GROUP BY SR.Dept, SR.Count
ORDER BY SR.[Count] DESC

Note that this is ordering by descending Count like your sample result does. If there's some other column that's not shown that should be used for ordering just replace Count in each of the ORDER BY clauses.

SQL Fiddle Demo