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.
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
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.
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
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
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;
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)
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
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
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;
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.
SELECT g.columnB as "ColumnB",
SUM(g.group_sum) over (ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) as "ColumnA"
FROM (
SELECT SUM(ColumnA) as group_sum,
ColumnB
FROM cand
GROUP BY ColumnB
ORDER BY ColumnB) g
Grouping ColumnB with SUM aggregation of ColumnA. And then applying window function to ColumnA to generate cumulative sum.
That was my question too and I used answers here. With more research I found another solution which is more optimized and easier, also more fun! This solutions is based on Window Functions. here it is:
--- creating table and inserting values of the question
CREATE TABLE #tmp ( ColumnA INT , ColumnB VARCHAR(1))
INSERT INTO #tmp
VALUES (2,'a'),(3,'b'),(4,'c'),(5,'d'),(1,'a')
---- my solution
SELECT
SUM(ColumnA) OVER (ORDER BY ColumnB ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) ColumnA
,ColumnB
FROM
(
SELECT SUM(ColumnA) ColumnA,ColumnB FROM #tmp GROUP BY ColumnB
) X
And the result is:
ColumnA ColumnB
----------- -------
3 a
6 b
10 c
15 d
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
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;
