如何在SQL中计算运行总计 [英] 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 |
+----------+-------+--------+---------------+

我需要计算运行总计列，即累积列.如何在SQL中高效地进行操作?

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

推荐答案

您可以在OVER子句中将SUM与ORDER BY子句一起使用:

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

这篇关于如何在SQL中计算运行总计的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！