Sum the column value incrementally with the same ID column

Question

I am having trouble in querying in SQL Server 2008, I searched the internet but I found nothing or it is not giving me any ideas on how to do it.

Using Northwind database, I need to query the table OrderDetails and select OrderID and UnitPrice showing something like this,

OrderID - UnitPrice ------------------------ 10248 - 14.00 10248 - 9.80 10248 - 34.80 10249 - 18.60

Result should be:

OrderID - UnitPrice ------------------------ 10248 - 14.00 10248 - 23.80 10248 - 58.6 10249 - 18.60

I'm also curious on how this can be solved without using a temporary table to store the intermediate sums. — Axarydax
– Axarydax, Commented May 14, 2013 at 4:51
Possible duplicate of stackoverflow.com/questions/11664142/… — TechDo
– TechDo, Commented May 14, 2013 at 4:58

TechDo · Accepted Answer · 2013-05-14 05:19:02Z

Please check:

;with T as( select *, ROW_NUMBER() over (partition by OrderID order by OrderID) RNum from YourTable ) select *, (select sum(UnitPrice) from T b where b.OrderID=a.OrderID and b.RNum<=a.RNum) CumTotal From T a

Try in SQL Fiddle

WOW! it works!... thanks a lot @techdo . . . I am learning here... thanks, have a great day....

Freelancer · Accepted Answer · 2013-05-14 04:54:20Z

Can refer following:

SELECT t1.id, t1.unitprice, SUM(t2.unitprice) AS SUM FROM t t1 INNER JOIN t t2 ON t1.id >= t2.id GROUP BY t1.id, t1.unitprice ORDER BY t1.id

Demo:

SQLFIDDLE DEMO

-1, incorrect. He wants to sequentially sum the rows with the same ID, not consecutive IDs.

Collectives™ on Stack Overflow

Sum the column value incrementally with the same ID column

2 Answers 2

1 Comment

1 Comment

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

1 Comment

1 Comment

Linked

Related