Sign in
 

none
Distinct Cummulative count

 > 

    Question

  • Question
    Sign in to vote
    0
    Sign in to vote

    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

  • Question
    Sign in to vote
    1
    Sign in to vote 
    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
  • Question
    Sign in to vote
    0
    Sign in to vote

    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

  • Question
    Sign in to vote
    0
    Sign in to vote

    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
    |
  • Question
    Sign in to vote
    0
    Sign in to vote

    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
  • Question
    Sign in to vote
    0
    Sign in to vote
    Using 2008 R2
    Thursday, August 07, 2014 6:53 PM
    |
  • Question
    Sign in to vote
    1
    Sign in to vote 
    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
  • Question
    Sign in to vote
    0
    Sign in to vote

    Thank you, Jingyang Li

    Thursday, August 07, 2014 7:27 PM
    |
  • Question
    Sign in to vote
    0
    Sign in to vote
    Thank you, Naomi
    Thursday, August 07, 2014 7:28 PM
    |