How to get running sum of a column in sql server

Question

Hi I have a column with name Qty from table Bills i want a column that show the running sum of Qty column like this :

Qty   Run_Sum
1      1
2      3
3      6
4      10
5      15

Suggest me some appropriate method to make running some thankx

Solution 1

if you RDBMS supports window function,

for SQL Server 2012

SELECT  Qty,
        SUM(Qty) OVER (ORDER BY Qty) AS CumulativeTOTAL
FROM    tableName

• SQLFiddle Demo

for SQL Server 2008

SELECT a.Qty, (SELECT SUM(b.Qty)
               FROM   TableName b
               WHERE  b.Qty <= a.Qty)
FROM   TableName a
ORDER  BY a.Qty;

• SQLFiddle Demo

Solution 2

SQLFiddle demo

SELECT Qty,
SUM(Qty) OVER (ORDER BY Qty) Run_Sum
FROM t ORDER BY Qty

For SQLServer prior to 2012:

select Qty,
(select sum(Qty) from t where Qty<=t1.Qty)
from t t1 order by Qty

SQLFiddle demo

Or also you can do it without subquery:

select t1.Qty, sum(t2.Qty)
from t t1 
join t t2 on (t1.Qty>=t2.Qty)
group by t1.Qty
order by t1.Qty

SQLFiddle demo

Solution 3

Here's a sample using Oracle/analytical functions:

select id, qty, sum(qty) over(order by id asc) run_sum
from test;

http://www.sqlfiddle.com/#!4/3d149/1

Comments

[user1448783]

Dear i am using sql server please provide some sample code in sql server

[Lloyd Santos]

Yep, thanks for pointing that out. Here's an updated query (sqlfiddle.com/#!3/6ac1f/5): select id, qty, (select sum(qty) from test where id <= t.id) run_sum from test t;

[valex]

added query for prior SQLServer versions

[John Woo]

@a_horse_with_no_name yes, but i was referring only to versions of sql server :)

[vikas]

I think subquery would be slow

[Kiril]

Your answer only works, if quantity is unique. How would that work, if there are duplicate quantities?

[Anuj Tripathi]

@Valex I didn't find anything unique in solution #1 which everyone is mentioning that it won't work prior version of SQL Server 2012; this also work in SQL Server 2008 R2+ (sqlfiddle.com/#!3/bafd7/6). Am I missing anything?

[valex]

@AnujTripathi OVER clause is implemented starting from SQL Server 2008 R2

[Anuj Tripathi]

@Valex Thanks for prompt response. Apparently, I've started my SQL journey from SQL Server 2008 :) +1 though !