CODEWITHSUNDEEP
sqloracle-database
user2786962
user2786962

Reputation: 469

How to calculate running total in SQL

I have my dataset in the given format It's a month level data along with salary for each month. I need to calculate cumulative salary for each month end. How can I do this

+----------+-------+--------+---------------+
| Account  | Month | Salary | Running Total |
+----------+-------+--------+---------------+
| a        |     1 |    586 |           586 |
| a        |     2 |    928 |          1514 |
| a        |     3 |    726 |          2240 |
| a        |     4 |    538 |           538 |
| b        |     1 |    956 |          1494 |
| b        |     3 |    667 |          2161 |
| b        |     4 |    841 |          3002 |
| c        |     1 |    826 |           826 |
| c        |     2 |    558 |          1384 |
| c        |     3 |    558 |          1972 |
| c        |     4 |    735 |          2707 |
| c        |     5 |    691 |          3398 |
| d        |     1 |    670 |           670 |
| d        |     4 |    838 |          1508 |
| d        |     5 |   1000 |          2508 |
+----------+-------+--------+---------------+

I need to calculate running total column which is cumulative column. How can I do efficiently in SQL?

Upvotes: 3

Views: 11434

Answers (1)

Giorgos Betsos
Giorgos Betsos

Reputation: 72175

You can use SUM with ORDER BY clause inside the OVER clause:

SELECT Account, Month, Salary, 
       SUM(Salary) OVER (PARTITION BY Account ORDER BY Month) AS RunningTotal
FROM mytable

Upvotes: 7

Related Questions