sum of a column upto the current row for each row
-
Hi All, How to get sum of a column upto the current row for each row into a different column in SQL Server.
Thanks & Regards, Md. Abdul Aleem NIIT technologies
-
Hi All, How to get sum of a column upto the current row for each row into a different column in SQL Server.
Thanks & Regards, Md. Abdul Aleem NIIT technologies
Not sure how this differs from your earlier question http://www.codeproject.com/Messages/3527850/Complex-Query.aspx[^] You're looking for running totals.
Data Quality Tools and Data Profiling software
-
Hi All, How to get sum of a column upto the current row for each row into a different column in SQL Server.
Thanks & Regards, Md. Abdul Aleem NIIT technologies
Not a full solution, but a step towards it: Let us assume a table "Table1" with following columns and values:
ID val total
1 12 <NULL>
2 23 <NULL>
3 2 <NULL>
4 14 <NULL>Now you can update the "total" column for each row with a query like the one below for ID=3:
UPDATE table1
SET total = tmp.result
FROM (SELECT MAX(ID) AS maxid, SUM(val) AS result
FROM Table1
WHERE (ID <= 3)) TMP
WHERE table1.id = tmp.maxidNext, replace "3" by a parameter and run the query for every ID value...
-
Not a full solution, but a step towards it: Let us assume a table "Table1" with following columns and values:
ID val total
1 12 <NULL>
2 23 <NULL>
3 2 <NULL>
4 14 <NULL>Now you can update the "total" column for each row with a query like the one below for ID=3:
UPDATE table1
SET total = tmp.result
FROM (SELECT MAX(ID) AS maxid, SUM(val) AS result
FROM Table1
WHERE (ID <= 3)) TMP
WHERE table1.id = tmp.maxidNext, replace "3" by a parameter and run the query for every ID value...
bhiller wrote:
run the query for every ID value...
What with a cursor or a while loop, what happens if he has 100k IDs. This is a bad solution, using the running total tools will give a much better result.
Never underestimate the power of human stupidity RAH