CODEWITHSUNDEEP
sqlsql-servergroup-by
bellfeige
bellfeige

Reputation: 11

Running cumulative count group by month

I have an table with values like this:

 count     month-year
    6         12-2020
    5         12-2020
    4         11-2020
    3         11-2020
    3         10-2020
    2         10-2020
    2         09-2020
    1         09-2020

I want to group the data by the month and show the sum of the count for the current month and the months before it. I am expecting the following output:

count     month-year
26        12-2020      <- month 12 count equal to month 12 sum + count start from month 9
15        11-2020      <- month 11 count equal to month 11 sum + count start from month 9
8         10-2020      <- month 10 count equal to month 9 sum + month 10
3         09-2020      <- assume month 9 is the launch month, count = sum count of month 9

Upvotes: 0

Views: 1348

Answers (2)

Talha Ramzan
Talha Ramzan

Reputation: 128

There is another way to calculate the desired result

select Distinct [month-year] , 
SUM(count) OVER (ORDER BY [month-year]) AS count
from yourTable
order by [month-year] desc

Upvotes: 0

Tim Biegeleisen
Tim Biegeleisen

Reputation: 520878

You want to use SUM here twice, both as an aggregate and as an analytic function:

SELECT
    [month-year],
    SUM(SUM(count)) OVER (ORDER BY [month-year]) AS count
FROM yourTable
GROUP BY
    [month-year]
ORDER BY
    [month-year] DESC;

screen capture from demo link below

Demo

Upvotes: 2

Related Questions