'Cumulating value of current row + sum of previous rows
How would you do to transform a Column in a table from this:
ColumnA ColumnB
2 a
3 b
4 c
5 d
1 a
to this:
ColumnA ColumnB
3 a
6(=3+3) b
10(=4+3+3) c
15(=5+4+3+3) d
I'm interested to see esp. what method you would pick.
Solution 1:[1]
Like this:
;WITH cte
AS
(
SELECT ColumnB, SUM(ColumnA) asum
FROM @t
gROUP BY ColumnB
), cteRanked AS
(
SELECT asum, ColumnB, ROW_NUMBER() OVER(ORDER BY ColumnB) rownum
FROM cte
)
SELECT (SELECT SUM(asum) FROM cteRanked c2 WHERE c2.rownum <= c1.rownum),
ColumnB
FROM cteRanked c1;
This should give you:
ColumnA ColumnB
3 a
6 b
10 c
15 d
Here is a live demo
Solution 2:[2]
I'd generally avoid trying to do so, but the following matches what you've asked for:
declare @T table (ColumnA int,ColumnB char(1))
insert into @T(ColumnA,ColumnB) values
(2 , 'a'),
(3 , 'b'),
(4 , 'c'),
(5 , 'd'),
(1, 'a')
;With Bs as (
select distinct ColumnB from @T
)
select
SUM(t.ColumnA),b.ColumnB
from
Bs b
inner join
@T t
on
b.ColumnB >= t.ColumnB
group by
b.ColumnB
Result:
ColumnB
----------- -------
3 a
6 b
10 c
15 d
For small data sets, this will be fine. But for larger data sets, note that the last row of the table relies on obtaining the SUM over the entire contents of the original table.
Solution 3:[3]
Not sure if this is optimal, but how about (SQL Fiddle):
SELECT x.A + COALESCE(SUM(y.A),0) ColumnA, x.ColumnB
FROM
(
SELECT SUM(ColumnA) A, ColumnB
FROM myTable
GROUP BY ColumnB
) x
LEFT OUTER JOIN
(
SELECT SUM(ColumnA) A, ColumnB
FROM myTable
GROUP BY ColumnB
) y ON y.ColumnB < x.ColumnB
GROUP BY x.ColumnB, x.A
Solution 4:[4]
Try the below script,
DECLARE @T TABLE(ColumnA INT, ColumnB VARCHAR(50));
INSERT INTO @T VALUES
(2, 'a'),
(3, 'b'),
(4, 'c'),
(5, 'd'),
(1, 'a');
SELECT SUM(ColumnA) OVER(ORDER BY ColumnB) AS ColumnA,ColumnB
FROM ( SELECT SUM(ColumnA) AS ColumnA,ColumnB
FROM @T GROUP BY ColumnB )T
Solution 5:[5]
create table #T
(
ID int primary key,
ColumnA int,
ColumnB char(1)
);
insert into #T
select row_number() over(order by ColumnB),
sum(ColumnA) as ColumnA,
ColumnB
from YourTable
group by ColumnB;
with C as
(
select ID,
ColumnA,
ColumnB
from #T
where ID = 1
union all
select T.ID,
T.ColumnA + C.ColumnA,
T.ColumnB
from #T as T
inner join C
on T.ID = C.ID + 1
)
select ColumnA,
ColumnB
from C
option (maxrecursion 0);
drop table #T;
Solution 6:[6]
Using SQL SERVER? SO
Let think you have a table with 3 column C_1, C_2, C_3 and ordered by C_1. Simply use [Over (Order By C_1)] to add a column for sum of C_3:
Select C_1, C_2, C_3, Sum(C_3) Over (Order By C_1)
if you want row number too, do it in the same way:
Select Row_Number() Over (Order By C_1), C_1, C_2, C_3, Sum(C_3) Over (Order By C_1)
Solution 7:[7]
If you are using SQL Server 2012 or greater then this will produce the required result.
DECLARE @t TABLE(
ColumnA int,
ColumnB varchar(50)
);
INSERT INTO @t VALUES
(2,'a'),
(3,'b'),
(4,'c'),
(5,'d'),
(1,'a');
SELECT
SUM(ColumnA) OVER (ORDER BY ColumnB ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) AS ColumnA,
ColumnB
FROM (
SELECT
ColumnB,
SUM(ColumnA) AS ColumnA
FROM @t
GROUP BY ColumnB
) DVTBL
ORDER BY ColumnB
Solution 8:[8]
DECLARE @t TABLE(ColumnA INT, ColumnB VARCHAR(50));
INSERT INTO @t VALUES
(2, 'a'),
(3 , 'b'),
(4 , 'c'),
(5 , 'd'),
(1 , 'a');
;WITH cte
AS
(
SELECT ColumnB, sum(ColumnA) value,ROW_NUMBER() OVER(ORDER BY ColumnB) sr_no FROM @t group by ColumnB
)
SELECT ColumnB
,SUM(value) OVER ( ORDER BY ColumnB ROWS BETWEEN UNBOUNDED PRECEDING AND 0 PRECEDING)
FROM cte c1;
Solution 9:[9]
The best solution (simplest and quickest) is to use a OVER(ORDER BY) clause.
I will give and explain my problem and the solution found.
I have a table containing some annual transaction that have following columns
Yearx INT
NoSeq INT
Amount DECIMAL(10,2)
Balance DECIMAL(10,2)
The first three columns have values; balance column is empty.
Problem
How to fill Balance values considering that first value at 1 January is 5000€ ?
Example
NoSeq Amount Balance
----- -------- ---------
1 120.00+ 5120.00+ <= 5000 + 120
2 16.00- 5104.00+ <= 5000 + 120 - 16
3 3000.00- 2104.00+ <= 5000 + 120 - 16 + 3000
4 640.00+ 2740.00+ <= 5000 + 120 - 16 + 3000 + 640
Solution (based on Abdul Rasheed answer)
WITH
t AS
(
SELECT NoSeq
,Amount
FROM payements
WHERE Yearx = 2021
)
SELECT NoSeq
,Amount
,1179.18 + SUM(Amount) OVER(ORDER BY NoSeq
ROWS BETWEEN UNBOUNDED PRECEDING
AND CURRENT ROW
) AS Balance
FROM t
In considering that on PostGreSql ROW BETWEEN used before is default, previous SELECT can be reduced to
WITH
t AS
(
SELECT NoSeq
,Amount
FROM payements
WHERE Yearx = 2021
)
SELECT NoSeq
,Amount
,1179.18 + SUM(Amount) OVER(ORDER BY NoSeq) as balance
FROM t
The first part (WITH clause) is used to define table on which OVER(ORDER BY) is apply in final SELECT.
The second part compute running sum using temporaty T table.
In my case, WITH clause is not necessary and SELECT command can be ultimely reducted to following SQL command
SELECT NoSeq
,Amount
,1179.18 + SUM(Amount) OVER(ORDER BY NoSeq) as balance
FROM payements
WHERE Yearx = 2021
I use this last SQL command in my VB.Net - Postgresql application.
To compute more that one year knowing Balance value on 1 January 2010, I use following SQL command
SELECT Yearx
,NoSeq
,Amount
,-279.34 + SUM(Amount) OVER(ORDER BY Yearx,NoSeq) as balance
FROM payements
WHERE Yearx BETWEEN 2010 AND 2021
Solution 10:[10]
You can do in this way also:
WITH grpAllData
AS
(
SELECT ColumnB, SUM(ColumnA) grpValue
FROM table_Name
gROUP BY ColumnB
)
SELECT g.ColumnB, sum(grpValue) OVER(ORDER BY ColumnB) desireValue
FROM grpAllData g
order by ColumnB
In the above query, We first aggregate all values in the same group, then in the final select just applied a window function on the previous result.
Solution 11:[11]
This will work based on grouping of columns cumulative summation for a column.
See the below SQL
SELECT product,
product_group,
fiscal_year,
Sum(quantity) OVER ( partition BY fiscal_year,a.product_group ORDER BY a.posting_date, a.product_group rows 100000000 PRECEDING) AS quantity
FROM report
WHERE
order by b.fiscal_year DESC
Solution 12:[12]
You can use below simple select statement for the same
SELECT COLUMN_A, COLUMN_B,
(SELECT SUM(COLUMN_B) FROM #TBL T2 WHERE T2.ID <= T1.ID) as SumofPreviousRow FROM #TBL T1;
Sources
This article follows the attribution requirements of Stack Overflow and is licensed under CC BY-SA 3.0.
Source: Stack Overflow