CODEWITHSUNDEEP
sql-servert-sql
Mike Mirabelli
Mike Mirabelli

Reputation: 410

Cumulative Total for Count -SQL

I have the following query:

Select 
    DATEPART(m,CREDAT_0) as Month, 
    COUNT(CREDAT_0) as count,
    SUM(COUNT(*)) OVER () as totalcount 
from 
    x3v6.CICPROD.ITMFACILIT
group by
    DATEPART(m,CREDAT_0)
order by 
    Month asc

Which returns the result set:

Month count totalcount
    1   56  2713
    2   12  2713
    3   39  2713
    4   36  2713
    5   54  2713
    6   32  2713
    7   27  2713
    8   12  2713
    9   32  2713
    10  20722713
    11  187 2713
    12  154 2713

But I want to see a cumulative total of products created by month for the year. So a column like this:

Month count totalcount cumtotal
    1   56  2713           56
    2   12  2713           68
    3   39  2713           107
    4   36  2713           etc..
    5   54  2713
    6   32  2713
    7   27  2713
    8   12  2713
    9   32  2713
    10  20722713
    11  187 2713
    12  154 2713           2713

How would I accomplish this? (if a way exists). I am using T-SQL in SQL Server 2012.

Thank you

Upvotes: 3

Views: 252

Answers (2)

Kannan Kandasamy
Kannan Kandasamy

Reputation: 13959

you can write query like below:

sum([count]) over(order by CREDAT_0) as CumulativeCount

Upvotes: 0

John Cappelletti
John Cappelletti

Reputation: 81930

For the last column

...
, COUNT(*) OVER (Order by CREDAT_0) as cumcount 
...

Upvotes: 4

Related Questions