How would you do to transform a Column in a table from this:
I'm interested to see esp. what method you would pick.
;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
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.
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;
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;