none
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.

    Appreciate your help..

    Thanks.

    Thursday, August 07, 2014 6:36 PM

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 Sahir88 Thursday, August 07, 2014 7:27 PM
    Thursday, August 07, 2014 7:14 PM
    Moderator
  • 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
    Moderator

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 Sahir88 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
    Moderator
  • 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 Sahir88 Thursday, August 07, 2014 7:27 PM
    Thursday, August 07, 2014 7:14 PM
    Moderator
  • Thank you, Jingyang Li

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