SUM Column in SQL

Question

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?

Answer 1

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)