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, May 14 '13 at 04:51
Possible duplicate of http://stackoverflow.com/questions/11664142/cumulative-total-in-ms-sql-server — TechDo, May 14 '13 at 04:58

TechDo · Accepted Answer · 2013-05-14T05:19:02.147

5

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

edited May 14 '13 at 05:19

answered May 14 '13 at 05:04

TechDo

18,398
3
51
64

WOW! it works!... thanks a lot @techdo . . . I am learning here... thanks, have a great day.... – Markmeplease May 14 '13 at 23:48

score -2 · Answer 2 · answered May 14 '13 at 04:54

-2

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

answered May 14 '13 at 04:54

Freelancer

9,008
7
42
81

-1, incorrect. He wants to sequentially sum the rows with the **same** ID, not consecutive IDs. – Matthew May 14 '13 at 04:55

Sum the column value incrementally with the same ID column

2 Answers2