Cumulative count with group by and case when

Question

I have been searching for this and trying to make this code work for a week now.

My data set tbSubscriptions has the columns:

Subscription_Date (dd/mm/yyyy hh:mm:ss)  
Subscription_Id (char 6)  
Subscription_Type (char 1)

To retrieve the number of subscriptions per week I use:

select   
   datepart(wk,Subscription_Date) as WeekNo,   
   sum(case when Subscription_Type = 1 then 1 else 0 end) as TotalSubcriptions  
from tbSubscriptions  
group by datepart(wk,Subscription_Date)  
order by 1

This query returns:

WeekNo  |Total Subscriptions  
21      |12  
22      |13  
23      |8  
24      |18

What I want is a Query that returns:

WeekNo  |CumulativeSubscriptions  
21      |12  
22      |25 (=12+13)  
23      |33 (=25+8)  
24      |51 (=33+18)

Here is a sample data set creation script:

GO
IF OBJECT_ID('tbSubscriptions') IS NOT NULL
  DROP TABLE tbSubscriptions
GO
CREATE TABLE tbSubscriptions (Subscription_Id INT, Subscription_Date datetime, Subscription_Type INT)
GO 
INSERT INTO tbSubscriptions (Subscription_Id, Subscription_Date, Subscription_Type) 
VALUES
(1, convert(datetime,'01-08-16 00:00:00 AM',5),1), 
(2, convert(datetime,'15-08-16 00:00:00 AM',5),1), 
(3, convert(datetime,'01-09-16 00:00:00 AM',5),1), 
(4, convert(datetime,'09-09-16 00:00:00 AM',5),1), 
(5, convert(datetime,'18-09-16 00:00:00 AM',5),0), 
(6, convert(datetime,'15-10-16 00:00:00 AM',5),1), 
(7, convert(datetime,'22-10-16 00:00:00 AM',5),0), 
(8, convert(datetime,'23-10-16 00:00:00 AM',5),0), 
(9, convert(datetime,'01-11-16 00:00:00 AM',5),1), 
(10, convert(datetime,'02-11-16 00:00:00 AM',5),1), 
(11, convert(datetime,'14-11-16 00:00:00 AM',5),0), 
(12, convert(datetime,'01-12-16 00:00:00 AM',5),1), 
(13, convert(datetime,'02-12-16 00:00:00 AM',5),1), 
(14, convert(datetime,'05-12-16 00:00:00 AM',5),1), 
(15, convert(datetime,'09-12-16 00:00:00 AM',5),1), 
(16, convert(datetime,'10-12-16 00:00:00 AM',5),1), 
(17, convert(datetime,'11-12-16 00:00:00 AM',5),1), 
(18, convert(datetime,'19-12-16 00:00:00 AM',5),0),
(19, convert(datetime,'25-12-16 00:00:00 AM',5),0),
(20, convert(datetime,'29-12-16 00:00:00 AM',5),0);
GO

I have tried both methods for cumulative sum (Window Functions and Self Joins), but couldn't get to work.

Any help would be much appreciated.

Kind regards, Paulo.

IT WORKED WITH "SELECT datepart(wk,Subscription_Date), ( SELECT Count(Subscription_Id) FROM tbSubscriptions t2 WHERE datepart(wk,t2.Subscription_Date)<=datepart(wk,t1.Subscription_Date) and t2.Subscription_Type= 1 ) AS running_total FROM tbSubscriptions t1 WHERE t1.Subscription_Type= 1 GROUP BY datepart(wk,t1.Subscription_Date) ORDER BY datepart(wk,t1.Subscription_Date)" — João Alqueres, Dec 05 '16 at 02:22

Felix Pamittan · Answer 1 · 2016-12-05T02:05:47.893

0

You can use CROSS APPLY:

with cte as(
    select   
        datepart(wk,Subscription_Date) as WeekNo,   
        sum(case when Subscription_Type = 1 then 1 else 0 end) as TotalSubcriptions
    from tbSubscriptions  
    group by datepart(wk,Subscription_Date)  
)
select
    c.Weekno,
    t.TotalSubcriptions
from cte c
cross apply(
    select sum(TotalSubcriptions) as TotalSubcriptions
    from cte
    where WeekNo <= c.WeekNo
) t
order by WeekNo

If you're using SQL Server 2012 and above, you can use the SUM OVER function:

with cte as(
    select   
       datepart(wk,Subscription_Date) as WeekNo,   
       sum(case when Subscription_Type = 1 then 1 else 0 end) as TotalSubcriptions  
    from tbSubscriptions  
    group by datepart(wk,Subscription_Date)  
)
select
    Weekno,
    sum(TotalSubcriptions) over(order by WeekNo) as CumulativeSubscriptions
from cte
order by WeekNo

edited Dec 05 '16 at 02:05

answered Dec 04 '16 at 22:31

Felix Pamittan

31,544
7
41
67

Thanks but Im running Microsoft SQL Server 2008 R2 and it doesnt seem to support running agregates with OVER (ORDER BY...) More here: http://stackoverflow.com/questions/12541355/how-to-use-partition-by-and-order-by-in-over-function Any thoughts? – João Alqueres Dec 05 '16 at 01:59
@JoãoAlqueres Try the `CROSS APPLY` solution. – Felix Pamittan Dec 05 '16 at 02:06
Thanks Felix ! It worked with another method by @KthProg at http://stackoverflow.com/questions/860966/calculate-a-running-total-in-sql-server Thank you so much anyway! – João Alqueres Dec 05 '16 at 02:26

Vamsi Prabhala · Answer 2 · 2016-12-05T02:23:21.193

Just use the sum window function.

select distinct 
   datepart(wk,Subscription_Date) as WeekNo,   
   sum(case when Subscription_Type = 1 then 1 else 0 end) 
   over(partition by datepart(wk,Subscription_Date) 
        order by datepart(wk,Subscription_Date)) as TotalSubcriptions  
from tbSubscriptions  
--group by datepart(wk,Subscription_Date)  
order by 1

Edit: Without using window functions, you can use a correlated sub query.

select datepart(week,subscription_date) wk
,count(case when subscription_type=1 then 1 end) + 
(select count(case when subscription_type=1 then 1 end) 
 from tbsubscriptions t1 
 where datepart(week,t.subscription_date)>datepart(week,t1.subscription_date)) cnt
from tbsubscriptions t
group by datepart(week,subscription_date)

Thanks but Im running Microsoft SQL Server 2008 R2 and it doesnt seem to support running agregates with OVER (ORDER BY...) More here: http://stackoverflow.com/questions/12541355/how-to-use-partition-by-and-order-by-in-over-function Any thoughts? — João Alqueres, Dec 05 '16 at 01:59

LukStorms · Answer 3 · 2017-03-15T06:30:45.690

SQL Server 2008 version

with cte as (
   select
    datepart(year,Subscription_Date) as YearNo,
    datepart(week,Subscription_Date) as WeekNo,
    sum(case when Subscription_Type = 1 then 1 else 0 end) as Total
   from tbSubscriptions
   group by datepart(year,Subscription_Date), datepart(week,Subscription_Date)
)
select 
--t1.YearNo,
t1.WeekNo,
sum(t2.Total) as TotalSubcriptions
from cte t1
inner join cte t2 on (t1.YearNo*100+t1.WeekNo >= t2.YearNo*100+t2.WeekNo)
group by t1.YearNo, t1.WeekNo
order by t1.YearNo, t1.WeekNo;

SQL Server 2012 version

select 
--YearNo,
WeekNo,
sum(Total) over (order by YearNo, WeekNo) as TotalSubcriptions
from (
   select
    datepart(year,Subscription_Date) as YearNo, 
    datepart(week,Subscription_Date) as WeekNo,
    sum(iif(Subscription_Type = 1,1,0)) as Total
   from tbSubscriptions
   group by datepart(year,Subscription_Date), datepart(week,Subscription_Date)
) q
order by YearNo, WeekNo;

Thanks but Im running Microsoft SQL Server 2008 R2 and it doesnt seem to support running agregates with OVER (ORDER BY...) More here: http://stackoverflow.com/questions/12541355/how-to-use-partition-by-and-order-by-in-over-function Any thoughts? — João Alqueres, Dec 05 '16 at 01:59

John Cappelletti · Answer 4 · 2016-12-05T04:17:36.123

0

Rather than the conditional aggregation, how about putting Subscription_Type in the WHERE

Select Distinct 
       WeekNo            = datepart(wk,Subscription_Date)
      ,TotalSubcriptions = sum(1) over (Order By datepart(wk,Subscription_Date))
From   tbSubscriptions  
Where  Subscription_Type = 1
Order  By 1

Edit 2008 version

;with cte as (
      Select WeekNo=datepart(wk,Subscription_Date)
            ,Cnt=count(*)
      From   tbSubscriptions
      Where  Subscription_Type = 1
      Group By datepart(wk,Subscription_Date)
) 
Select A.WeekNo 
      ,TotalSubcriptions = sum(B.Cnt)
 From  cte A
 Join  cte B on (A.WeekNo>=B.WeekNo)
 Group By A.WeekNo
 Order By 1

Returns

WeekNo  TotalSubcriptions
32      1
34      2
36      3
37      4
42      5
45      7
49      9
50      12
51      13

edited Dec 05 '16 at 04:17

answered Dec 04 '16 at 23:33

John Cappelletti

79,615
7
44
66

Thanks but Im running Microsoft SQL Server 2008 R2 and it doesnt seem to support running agregates with OVER (ORDER BY...) More here: http://stackoverflow.com/questions/12541355/how-to-use-partition-by-and-order-by-in-over-function Any thoughts? – João Alqueres Dec 05 '16 at 01:59
@JoãoAlqueres Fair enough. FYI, when you tag SQL Server, it is generally assumed currently supported versions which is 2012+. Just for for future reference, just be sure to mention 2008. – John Cappelletti Dec 05 '16 at 03:57

Cumulative count with group by and case when

4 Answers4