Answered by:
Distinct Cummulative count

-
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 Robo/p :
Month CountPerMonth DistinctCummulativeCount
7 3 3
8 4 6
9 1 6
10 1 7CountPerMonth=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.
Appreciate your help..
Thanks.
Question
Answers
-
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 SS88 Thursday, August 07, 2014 7:27 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- Marked as answer by Jingyang LiModerator Thursday, August 07, 2014 7:37 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 SS88 Thursday, August 07, 2014 6:48 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- Marked as answer by Jingyang LiModerator Thursday, August 07, 2014 7:37 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 SS88 Thursday, August 07, 2014 7:27 PM
-
-