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
Asked
Active
Viewed 6.8k times
15
John Woo
- 258,903
- 69
- 498
- 492
user1448783
- 263
- 3
- 5
- 9
-
How is that running sum? 1 1 2 3 5 8 13 21 34.....[The fibonacci series] would be an example of running sum. – Achrome Feb 19 '13 at 08:53
-
1What [RDBMS](http://en.wikipedia.org/wiki/Relational_database_management_system) you are using? `SQL Server`? `MySQL`? `Oracle`? `DB2`? etc.. – John Woo Feb 19 '13 at 08:54
-
I am using sql server 2008 – user1448783 Feb 19 '13 at 08:58
-
@user1448783 do you want data in select query for report purpose or you want a column in your table? – vikas Feb 19 '13 at 09:17
-
@user1448783 have you try my answer ? – vikas Feb 19 '13 at 09:27
6 Answers
12
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;
John Woo
- 258,903
- 69
- 498
- 492
12
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
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
valex
- 23,966
- 7
- 43
- 60
-
-
@a_horse_with_no_name yes, but i was referring only to versions of sql server `:)` – John Woo Feb 19 '13 at 09:09
-
Your answer only works, if `quantity` is unique. How would that work, if there are duplicate `quantities`? – Kiril Jun 01 '15 at 14:42
-
@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+ (http://sqlfiddle.com/#!3/bafd7/6). Am I missing anything? – Anuj Tripathi May 18 '16 at 08:19
-
1@AnujTripathi `OVER` clause is implemented starting from SQL Server 2008 R2 – valex May 18 '16 at 08:45
-
@Valex Thanks for prompt response. Apparently, I've started my SQL journey from SQL Server 2008 :) +1 though ! – Anuj Tripathi May 18 '16 at 09:01
1
Here's a sample using Oracle/analytical functions:
select id, qty, sum(qty) over(order by id asc) run_sum
from test;
Lloyd Santos
- 392
- 2
- 14
-
Dear i am using sql server please provide some sample code in sql server – user1448783 Feb 19 '13 at 09:00
-
Yep, thanks for pointing that out. Here's an updated query (http://www.sqlfiddle.com/#!3/6ac1f/5): `select id, qty, (select sum(qty) from test where id <= t.id) run_sum from test t;` – Lloyd Santos Feb 19 '13 at 09:07
0
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
vikas
- 2,780
- 4
- 27
- 37
0
;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
muhmud
- 4,474
- 2
- 15
- 22
0
@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
