Running Total For Distinct Count (T-SQL)



  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]


--SQL Server2012 or 2014
;with mycte as (
select [month] 
, row_number() over (partition by PersonName order by [Month]) as rn 
from   _Test
) 

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


drop table   _Test


 



 

http://social.msdn.microsoft.com/Forums/sqlserver/en-US/9b06a920-d721-43dc-ae67-cfaeba209565/distinct-cummulative-count?forum=transactsql

Advertisements


Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s