CODEWITHSUNDEEP
sqlsql-serversql-server-2008select
user1448783
user1448783

Reputation: 263

How to get running sum of a column in sql server

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

Upvotes: 15

Views: 68752

Answers (6)

Varun
Varun

Reputation: 763

@mahmud: See what this gives

DECLARE @Bills table
(
    QUANTITY int
)

INSERT INTO @Bills
SELECT 2 UNION ALL
SELECT 6 UNION ALL
SELECT 7 UNION ALL
SELECT 1 UNION ALL
SELECT 3 UNION ALL
SELECT -5 UNION ALL
SELECT 5 UNION ALL
select 1

;with cte as (
  select top 1 QUANTITY, QUANTITY as RunningSum
  from @Bills
  order by QUANTITY

  union all

  select t.QUANTITY, cte.RunningSum + t.QUANTITY
  from cte
  inner join @Bills t on cte.QUANTITY + 1 = t.QUANTITY
)
select * from cte

Upvotes: 0

valex
valex

Reputation: 24144

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

Upvotes: 12

muhmud
muhmud

Reputation: 4604

;with cte as (
  select top 1 Qty, Qty as RunningSum
  from Bills
  order by Qty

  union all

  select t.Qty, cte.RunningSum + t.Qty
  from cte
  inner join Bills t on cte.Qty + 1 = t.Qty
)
select * from cte

Upvotes: 0

vikas
vikas

Reputation: 2830

Check this

DECLARE @TEMP table
(
    ID int IDENTITY(1,1),
    QUANTITY int
)

INSERT INTO @TEMP
SELECT 1 UNION ALL
SELECT 2 UNION ALL
SELECT 3 UNION ALL
SELECT 4 UNION ALL
SELECT 8 UNION ALL
SELECT 7 UNION ALL
SELECT 5 UNION ALL
SELECT 1

SELECT t.QUANTITY AS Qty, SUM(t1.QUANTITY) AS Run_Sum
FROM @TEMP t
INNER JOIN @TEMP t1
ON t1.ID <= t.ID
GROUP BY t.ID, t.QUANTITY
ORDER BY t.ID

Upvotes: 0

John Woo
John Woo

Reputation: 263723

if you RDBMS supports window function,

for SQL Server 2012

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

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;

Upvotes: 12

Lloyd Santos
Lloyd Santos

Reputation: 402

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

Upvotes: 1

Related Questions