Collecting values from the upper rows

Question

I have a table in sql server and I want to summarize a cell with upper rows in T-Sql like this:

 Quantity          Total
-----------------------------
    1                1
    5                6
    12               18
    20               38

I use SQL server 2008, How can I achieve this?

Regards, Majid.

Answer 1

You are looking for a cumulative sum, it would appear. If you are using SQL Server 2012 or later, just do:

select quantity, sum(quantity) over (order by quantity)
from table t;

Note the order by quantity. You need to specify the ordering for the cumulative sum, typically doing another sum.

In earlier versions, you can do this using a correlated subquery:

select quantity,
       (select sum(t2.quantity)
        from table t2
        where t2.quantity <= t.quantity
       ) as total
from table t;