6

I have a table in SQL Server, and I need to sum a column, like the example below:

CREATE TABLE B
(
ID int,
Qty int,
)

INSERT INTO B VALUES (1,2)
INSERT INTO B VALUES (2,7)
INSERT INTO B VALUES (3,2)
INSERT INTO B VALUES (4,11)

SELECT *, '' AS TotalQty FROM B
ORDER BY ID

In this example what I need is the column TotalQty give me the values like:

 2
 9
 11
 22

How can it be achieved?

Vega
  • 27,856
  • 27
  • 95
  • 103
Marcelo Antunes
  • 165
  • 7

1 Answers1

7

You can use SUM in a co-related subquery or CROSS APPLY like this

Co-related Subquery

SELECT ID,(SELECT SUM(Qty) FROM B WHERE B.id <= C.id) FROM B as C
ORDER BY ID

Using CROSS APPLY

SELECT ID,D.Qty FROM B as C
CROSS APPLY
(
SELECT SUM(Qty) Qty 
FROM B WHERE B.id <= C.id
)AS D
ORDER BY ID

Output

1   2
2   9
3   11
4   22

If you were using SQL Server 2012 or above, SUM() with Over() clause could have been used like this.

SELECT ID, SUM(Qty) OVER(ORDER BY ID ASC) FROM B as C
ORDER BY ID

Edit

Another way to do this in SQL Server 2008 is using Recursive CTE. Something like this.

Note: This method is based on the answer by Roman Pekar on this thread Calculate a Running Total in SQL Server. Based on his observation this would perform better than co related subquery and CROSS APPLY both

;WITH CTE as
(
SELECT ID,Qty,ROW_NUMBER()OVER(ORDER BY ID ASC) as rn 
FROM B
), CTE_Running_Total as 
(
    SELECT Id,rn,Qty,Qty as Running_Total 
    FROM CTE
    WHERE rn = 1
    UNION ALL 
    SELECT C1.Id,C1.rn,C1.Qty,C1.Qty + C2.Running_Total as Running_Total
    FROM CTE C1 
    INNER JOIN CTE_Running_Total C2
    ON C1.rn = C2.rn + 1
)
SELECT * 
FROM CTE_Running_Total
ORDER BY Id
OPTION (maxrecursion 0)
Community
  • 1
  • 1
ughai
  • 9,830
  • 3
  • 29
  • 47
  • 1
    i just thinking same and u posted answer .. :).. but it slower if amount of data is too large.. – Anant Dabhi Jun 02 '15 at 08:49
  • 1
    @AnantDabhi - Yes you are correct. for large sets of data this will be slow. `SUM()` with `over(order by id asc)` could have been used however this is only supported in sql 2012 – ughai Jun 02 '15 at 08:53
  • "will it work for more than 10 million records". Yes it will. Will it be fast is based on your requirements, table and index structure. – ughai Jun 11 '15 at 07:21