SQL运行总计按ID分组

Using this Query, I need to populate the NULL column with running total for each row where it would correspond to the paid amount over the period of a calendar year, year to date, of the current table. This running total should be grouped by member_id.

使用此查询,我需要使用每行的运行总计填充NULL列,其中它对应于当前表的日历年的年度期间的付费金额。此运行总计应按member_id分组。

SELECT id=identity(int,1,1), cast(null as numeric(22,3)) as max_running_total, *
INTO #temp
FROM Customer_DB..Sales_Table
ORDER BY Date_Column asc

UPDATE #temp
SET max_running_total = (SELECT SUM(paid_amount)
                         FROM #temp
                         WHERE id <= id
                         GROUP BY member_id)

1 个解决方案

#1

Since you have not given the schema, I have taken a sample schema and have tried to a rolling sum. You can use the same sql windows functions and achieve your results

由于您没有给出架构,我已经采用了一个示例架构,并尝试了滚动总和。您可以使用相同的SQL窗口函数来实现您的结果

CREATE TABLE amt 
  ( 
     id            INT, 
     paid_amount   DECIMAL, 
     running_total DECIMAL 
  ) 

insert INTO amt VALUES (1, 100, NULL), (2, 50, NULL), (3, 50, NULL) 

SELECT id, paid_amount, 
 SUM(paid_amount) over(ORDER BY id ROWS BETWEEN unbounded preceding AND CURRENT ROW) AS running_total 
FROM amt