JosephEricDavis
asked on
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].[tbA
WHERE EndJobDateTime IS NOT NULL AND YEAR([EndJobDateTime])='20
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
SELECT YEAR([EndJobDateTime]) AS [Year]
,MONTH([EndJobDateTime]) AS [Month]
,COUNT(*) AS N
FROM [OutageManager].[dbo].[tbA
WHERE EndJobDateTime IS NOT NULL AND YEAR([EndJobDateTime])='20
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
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])ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Given the example you gave, 12+21 is not 163