Running total in Microsoft SSMS

user_11164060 0
Viewed 97

How to get running total in SSMS ?

select E.Employee,E.Month,E.Year,E.Salary,
SUM(E.Salary) over (partition by E.Year order by E.Employee) as Cum_Sal
from Employees E
group by E.Employee,E.Month,E.Year,E.Salary
order by E.Year

Output needed:

enter image description here

sql-server sql
2 Answers

Try this:

select E.Employee
      ,E.Month
      ,E.Year
      ,E.Salary
      ,SUM(E.Salary) OVER 
      (
            partition by E.Year 
            order by CASE E.Month
                          WHEN 'Jan' THEN 1
                          WHEN 'Feb' THEN 2
                          WHEN 'Mar' THEN 3
                          WHEN 'Apr' THEN 4
                          WHEN 'May' THEN 5
                          WHEN 'Jun' THEN 6
                          WHEN 'Jul' THEN 7
                          WHEN 'Aug' THEN 8
                          WHEN 'Sep' THEN 9
                          WHEN 'Oct' THEN 10
                          WHEN 'Nov' THEN 11
                          WHEN 'Dec' THEN 12
                      END
      ) as Cum_Sal
from Employees E
order by E.Year
user_10803540

SQL Server has pretty flexible methods for converting to a date. And your basic problem is that the rows don't have a convenient ordering -- the dates are stored as month names.

But you can easily convert to a date and use that for the ordering:

select e.*,  -- or whatever columns you want
       sum(e.Salary) over (partition by e.employee, e.Year
                           order by convert(date, '2000 ' + e.month)
                          ) as cumulative_salary
from Employees e
order by e.employee, e.Year;

Aggregation is not necessary for this query.

Another convenient method uses charindex():

       sum(e.Salary) over (partition by e.employee, e.Year
                           order by charindex(month, 'jan,feb,mar,apr,may,jun,jul,aug,sep,oct,nov,dec')
                          ) as cumulative_salary
user_11440350
Related