# Distinct Cummulative count

### Question

• I need to calculate the distinct Cummulative count for the incremental months

As shown below

Month PersonName
7 sam
7 kim
7 tim
8 sam
8 tom
8 rex
8 ram
9 sam
10 Rob

o/p :

Month CountPerMonth DistinctCummulativeCount
7 3  3
8 4  6
9 1  6
10 1  7

CountPerMonth=count for respective month

DistinctCummulativeCount=column should give distinct count for the months in (7),(7,8),(7,8,9), (7,8,9,10) respectively.

Thanks.

Thursday, August 07, 2014 6:36 PM

• ```create table _Test
(
[Month] Int,
PersonName varchar(10)
)

Insert into _Test
Select 7,'sam' union all
Select 7,'kim' union all
Select 7,'tim' union all
Select 8,'sam' union all
Select 8,'tom' union all
Select 8,'rex' union all
Select 8,'ram' union all
Select 9,'sam' union all
Select 10,'Rob'

;with mycte as (
select [Month] ,row_number() Over(Partition by PersonName Order by  [Month]) rn0
, row_number() Over(Partition by PersonName Order by  [Month]) rn from _Test
)

,mycte1 as (
SELECT a.[Month],   (SELECT SUM(Case WHen rn=1 Then 1 else 0 End)
FROM mycte b
WHERE b.rn <= a.rn and b.[Month]<=a.[Month]
) as runningTotal
FROM   mycte a
)

Select [Month], Count(*) CountPerMonth , Max(runningTotal) as DistinctCummulativeCount
From mycte1
GROUP BY  [Month]
ORDER BY [Month]

drop table   _Test

```

• Marked as answer by Thursday, August 07, 2014 7:27 PM
Thursday, August 07, 2014 7:14 PM
• Using SQL 2012 or up:

```declare @t table([month] int, PersonName varchar(10))
insert into @t values
(7, 'sam'),(7, 'kim'), (7, 'tim'), (8, 'sam'),(8, 'tom'),(8, 'rex'),(8, 'ram'),
(9, 'sam'), (10, 'Rob')

;with cte as (select *, row_number() over (partition by PersonName order by [Month]) as Rn
from @t)

select distinct [month], count(PersonName) over (partition by [month]) as CountPerMonth,
sum(case when Rn=1 then 1 else 0 end) over (order by [month]) as CummulativeDistinctCount
from cte ```

For every expert, there is an equal and opposite expert. - Becker's Law

My blog

My TechNet articles

Thursday, August 07, 2014 6:47 PM

### All replies

• I have done this way,which works fine but I don't wanted to hardcode the second cte....

create table _Test
(
[Month] Int,
PersonName varchar(10)
)

Insert into _Test
Select 7,'sam' union all
Select 7,'kim' union all
Select 7,'tim' union all
Select 8,'sam' union all
Select 8,'tom' union all
Select 8,'rex' union all
Select 8,'ram' union all
Select 9,'sam' union all
Select 10,'Rob'

;With Cte AS (
Select [Month],Count(*) [Count] from _Test
group by [Month]
)
, cte1 as (
Select '7'[Month],count(Distinct PersonName) DistinctCummulativeCount
from _Test where [Month] in (7) union all
Select '8'[Month],count(Distinct PersonName) DistinctCummulativeCount
from _Test where [Month] in (7,8) union all
Select '9'[Month],count(Distinct PersonName)DistinctCummulativeCount
from _Test where [Month] in (7,8,9) union all
Select '10'[Month],count(Distinct PersonName) DistinctCummulativeCount
from _Test where [Month] in (7,8,9,10)
) Select a.*,b.DistinctCummulativeCount from Cte a join cte1 b on a.[Month]=b.[Month]

• Edited by Thursday, August 07, 2014 6:48 PM
Thursday, August 07, 2014 6:46 PM
• Using SQL 2012 or up:

```declare @t table([month] int, PersonName varchar(10))
insert into @t values
(7, 'sam'),(7, 'kim'), (7, 'tim'), (8, 'sam'),(8, 'tom'),(8, 'rex'),(8, 'ram'),
(9, 'sam'), (10, 'Rob')

;with cte as (select *, row_number() over (partition by PersonName order by [Month]) as Rn
from @t)

select distinct [month], count(PersonName) over (partition by [month]) as CountPerMonth,
sum(case when Rn=1 then 1 else 0 end) over (order by [month]) as CummulativeDistinctCount
from cte ```

For every expert, there is an equal and opposite expert. - Becker's Law

My blog

My TechNet articles

Thursday, August 07, 2014 6:47 PM
• Using 2008 R2
Thursday, August 07, 2014 6:53 PM
• ```create table _Test
(
[Month] Int,
PersonName varchar(10)
)

Insert into _Test
Select 7,'sam' union all
Select 7,'kim' union all
Select 7,'tim' union all
Select 8,'sam' union all
Select 8,'tom' union all
Select 8,'rex' union all
Select 8,'ram' union all
Select 9,'sam' union all
Select 10,'Rob'

;with mycte as (
select [Month] ,row_number() Over(Partition by PersonName Order by  [Month]) rn0
, row_number() Over(Partition by PersonName Order by  [Month]) rn from _Test
)

,mycte1 as (
SELECT a.[Month],   (SELECT SUM(Case WHen rn=1 Then 1 else 0 End)
FROM mycte b
WHERE b.rn <= a.rn and b.[Month]<=a.[Month]
) as runningTotal
FROM   mycte a
)

Select [Month], Count(*) CountPerMonth , Max(runningTotal) as DistinctCummulativeCount
From mycte1
GROUP BY  [Month]
ORDER BY [Month]

drop table   _Test

```

• Marked as answer by Thursday, August 07, 2014 7:27 PM
Thursday, August 07, 2014 7:14 PM
• Thank you, Jingyang Li

Thursday, August 07, 2014 7:27 PM
• Thank you, Naomi
Thursday, August 07, 2014 7:28 PM