• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 903
  • Last Modified:

SQL Cumulative Sum on Rows

I this temporary table in SQL:

DUEMONTH      DUEYEAR      LATE      ACHIEVED      UNSURE      OUTSTANDING      TOTAL      MONTHNAME
1      2010      0      0      42      0      42      January 2010
2      2010      2      4      37      0      43      February 2010
3      2010      0      2      43      0      45      March 2010
4      2010      0      1      36      0      37      April 2010
5      2010      1      5      32      0      38      May 2010
6      2010      2      2      19      0      23      June 2010
7      2010      0      3      35      0      38      July 2010
8      2010      0      1      32      0      33      August 2010
9      2010      1      2      32      0      35      September 2010
10      2010      0      1      32      0      33      October 2010
11      2010      2      0      40      0      42      November 2010
12      2010      1      0      39      0      40      December 2010
1      2011      0      5      52      0      57      January 2011
2      2011      0      0      32      0      32      February 2011
3      2011      0      2      41      0      43      March 2011
4      2011      0      0      7      0      7      April 2011

I want to be able to calculate the cumulative sum on the ACHIEVED and TOTAL columns in 2 new columns.
So one column called ACHIEVED_CUMULATIVE will be:
0,4,6,7,12,13,15,16,18,19,19,19,24,24,26,26

How can i do this as some samples that ive seen require some sort of identity column which i dont really have.

Here is the code that i have to retrieve the first bit of data
SELECT MONTH(DUE_DATE) AS DUEMONTH, 
YEAR(DUE_DATE) AS DUEYEAR, 
SUM(CASE WHEN LATE = 1 THEN 1 ELSE 0 END) AS LATE, 
SUM(CASE WHEN ACHIEVED = 1 THEN 1 ELSE 0 END) AS ACHIEVED,  
SUM(CASE WHEN DODGY = 1 THEN 1 ELSE 0 END) AS DODGY, 
SUM(CASE WHEN OUTSTANDING = 1 THEN 1 ELSE 0 END) AS OUTSTANDING, 
SUM(CASE WHEN LATE = 1 THEN 1 ELSE 0 END) + SUM(CASE WHEN ACHIEVED = 1 THEN 1 ELSE 0 END) + SUM(CASE WHEN DODGY = 1 THEN 1 ELSE 0 END) + SUM(CASE WHEN OUTSTANDING = 1 THEN 1 ELSE 0 END) AS TOTAL,
DATENAME(m,DATEADD(mm,MONTH(DUE_DATE),-1)) + ' ' + CONVERT(NVARCHAR,YEAR(DUE_DATE)) as MONTHNAME
FROM #COUNTNEXT 
GROUP BY YEAR(DUE_DATE), MONTH(DUE_DATE) ORDER BY DUEYEAR, DUEMONTH

Open in new window

0
AVONFRS
Asked:
AVONFRS
  • 2
1 Solution
 
AkenathonCommented:
What's your DBMS brand and version?
0
 
AVONFRSAuthor Commented:
SQL Server 2008 R2
0
 
SharathData EngineerCommented:
try this.
;WITH CTE as (
SELECT MONTH(DUE_DATE) AS DUEMONTH, 
YEAR(DUE_DATE) AS DUEYEAR, 
SUM(CASE WHEN LATE = 1 THEN 1 ELSE 0 END) AS LATE, 
SUM(CASE WHEN ACHIEVED = 1 THEN 1 ELSE 0 END) AS ACHIEVED,  
SUM(CASE WHEN DODGY = 1 THEN 1 ELSE 0 END) AS DODGY, 
SUM(CASE WHEN OUTSTANDING = 1 THEN 1 ELSE 0 END) AS OUTSTANDING, 
SUM(CASE WHEN LATE = 1 THEN 1 ELSE 0 END) + SUM(CASE WHEN ACHIEVED = 1 THEN 1 ELSE 0 END) + SUM(CASE WHEN DODGY = 1 THEN 1 ELSE 0 END) + SUM(CASE WHEN OUTSTANDING = 1 THEN 1 ELSE 0 END) AS TOTAL,
DATENAME(m,DATEADD(mm,MONTH(DUE_DATE),-1)) + ' ' + CONVERT(NVARCHAR,YEAR(DUE_DATE)) as MONTHNAME
FROM #COUNTNEXT 
GROUP BY YEAR(DUE_DATE), MONTH(DUE_DATE) ),
CTE2 as (SELECT *,ROW_NUMBER() over (ORDER BY DUEYEAR,DUEMONTH) rn FROM CTE )
SELECT *,(SELECT SUM(ACHIEVED) FROM CTE2 C2 WHERE C2.rn <= C1.rn) ACHIEVED_CUMULATIVE 
  FROM CTE2 C1 
 ORDER BY DUEYEAR, DUEMONT

Open in new window

0
 
AVONFRSAuthor Commented:
That is perfect. Worked great thanks!
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Cloud Class® Course: Ruby Fundamentals

This course will introduce you to Ruby, as well as teach you about classes, methods, variables, data structures, loops, enumerable methods, and finishing touches.

  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now