We help IT Professionals succeed at work.

SQL Query - need a rolling total column

In MS SQL Server I have the following query...


SELECT YEAR([EndJobDateTime]) AS [Year]
      ,MONTH([EndJobDateTime]) AS [Month]
      ,COUNT(*) AS N
  FROM [OutageManager].[dbo].[tbApplication]
WHERE EndJobDateTime IS NOT NULL AND YEAR([EndJobDateTime])='2011'

GROUP BY Month([EndJobDateTime]), YEAR([EndJobDateTime])


Which returns the following data...
Year       Month  N
2011       1              4
2011       2              2
2011       3              6
2011       4              18
2011       5              15
2011       6              10
2011       7              12
2011       8              13
2011       9              12
2011       10           26
2011       11           24
2011       12           21

I need another column that would display the running total.  So the data would look like this.

Year      Month      N      Total
2011      1      4      4
2011      2      2      6
2011      3      6      12
2011      4      18      30
2011      5      15      45
2011      6      10      55
2011      7      12      67
2011      8      13      80
2011      9      12      92
2011      10      26      118
2011      11      24      142
2011      12      21      163

Thanks

Comment
Watch Question

Top Expert 2011

Commented:
How are you generating the total?

Given the example you gave, 12+21 is not 163
Top Expert 2011

Commented:
This assums that month is an integer data type:

SELECT YEAR([EndJobDateTime]) AS [Year]
      ,MONTH([EndJobDateTime]) AS [Month]
      ,COUNT(*) AS N,sum(Isnull([Month]),0) as Total
  FROM [OutageManager].[dbo].[tbApplication]
WHERE EndJobDateTime IS NOT NULL AND YEAR([EndJobDateTime])='2011'
GROUP BY Month([EndJobDateTime]), YEAR([EndJobDateTime])

Open in new window

Try this please.

Greg


SELECT YEAR([EndJobDateTime]) AS [Year],
       MONTH([EndJobDateTime]) AS [Month],
       N+COALESCE((SELECT SUM(N) 
                      FROM [OutageManager].[dbo].[tbApplication] b 
                      WHERE MONTH(b.[EndJobDateTime]) < MONTH(a.[EndJobDateTime])),0)
                         AS Total
FROM [OutageManager].[dbo].[tbApplication] a
WHERE EndJobDateTime IS NOT NULL AND YEAR([EndJobDateTime])='2011'
ORDER BY [Month]

Open in new window