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:

enter image description here

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?

can you add schema of you table or do you have any primary key ?? — Dhaval, Oct 14 '13 at 13:29
I guess this thread was not offered to you as you typed your question. http://stackoverflow.com/questions/860966/calculate-a-running-total-in-sqlserver By the way, my google search string was, "sql server running totals". — Dan Bracuk, Oct 14 '13 at 13:38
@Dhaval - there is no PK nor row numbers as the data are being historized via SCD. — DNac, Oct 14 '13 at 13:43
And if you didn't know the term "running total", you could very well search for "sql server cumulative count", that would give you *plenty* of suggestions as well. — Andriy M, Oct 14 '13 at 15:38

D Stanley · Accepted Answer · 2013-10-14T18:04:25.617

9

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

edited Oct 14 '13 at 18:04

answered Oct 14 '13 at 17:58

D Stanley

149,601
11
178
240

Great, this works perfectly for my needs. Is there also possibility to "easily" add another derived column next to the cumulative count, which would count the (cumulative_count / sum [per_all_depts]) for each row? In the meaning of cumulative_count / 254. The result should be shown as percentage. – DNac Oct 15 '13 at 10:20
Done, found it by myself. Thanks again, appreciate your help! – DNac Oct 15 '13 at 12:19

score 3 · Answer 2 · edited May 23 '17 at 10:29

3

I think you can use some temporary / variable table for this, and use solution from here:

declare @Temp table (rn int identity(1, 1) primary key, dept varchar(128), Total int)

insert into @Temp (dept, Total)
select
    dept, count(*) as Total
from SR
group by dept

;with cte as (
    select T.dept, T.Total, T.Total as Cumulative, T.rn
    from @Temp as T
    where T.rn = 1
    union all
    select T.dept, T.Total, T.Total + C.Cumulative as Cumulative, T.rn
    from cte as C
        inner join @Temp as T on T.rn = C.rn + 1
)
select C.dept, C.Total, C.Cumulative
from cte as C
option (maxrecursion 0)

sql fiddle demo

There're some other solutions, but this one is fastest for SQL Server 2008, I think.

edited May 23 '17 at 10:29

Community

1
1

answered Oct 14 '13 at 13:32

Roman Pekar

107,110
28
195
197

@ypercube nice article, thank you, but actually, in this article, cursor approach is 2 times slower than CTE (but it has less I/O operations) – Roman Pekar Oct 14 '13 at 13:58
Yes, right, I read the pictures wrong. I was sure (in my memory) it was the other way around. Perhaps I had discarded CTE method because it needs consecutive numbers (that you get through the temp table and IDENTITY.) Link again to the article discussed in these comments: [Best approaches for running totals.](http://www.sqlperformance.com/2012/07/t-sql-queries/running-totals) – ypercubeᵀᴹ Oct 14 '13 at 14:06
@ypercube yeah, cte is a bit less code than a cursor, but it needs numbers/other keys without gaps – Roman Pekar Oct 14 '13 at 14:11

score 1 · Answer 3 · answered May 26 '22 at 08:07

If it is possible to add an identity column to the table - then the solution is easier;

create table #SQLCumulativeCount
(
 id int identity(1,1),
 Dept varchar(100),
 Count int
)
insert into #SQLCumulativeCount (Dept,Count) values ('PMO',106)
insert into #SQLCumulativeCount (Dept,Count) values ('Finance',64)
insert into #SQLCumulativeCount (Dept,Count) values ('Operations',41)
insert into #SQLCumulativeCount (Dept,Count) values ('Infrastructure',22)
insert into #SQLCumulativeCount (Dept,Count) values ('HR',21)

select *,
   sum(Count) over(order by id rows unbounded preceding) as Cumulative 
from #SQLCumulativeCount

Teja Goud Kandula · Answer 4 · 2023-06-03T09:01:52.133

0

with Base as (
select 
    dept, 
    count, 
    ROW_NUMBER() OVER(order by count desc) as RowNum
from SR
)
select 
    dept, 
    count, 
    sum(count) over(order by RowNum) as Cumulative
from Base

edited Jun 03 '23 at 09:01

answered Mar 02 '23 at 03:38

Teja Goud Kandula

1,462
13
26

SQL Cumulative Count

4 Answers4

Linked