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
6 Answers
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;
1 Comment
vikas
I think subquery would be slow
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
6 Comments
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
:)
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 |
Here's a sample using Oracle/analytical functions:
select id, qty, sum(qty) over(order by id asc) run_sum
from test;
2 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;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
Comments
;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
Comments
@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
Comments
SQL Server?MySQL?Oracle?DB2? etc..