SUM SQL SERVER Column

Question

I have a table in SQL Server , and I need to sum the column as shown 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, I need the TotalQty column TotalQty give me values ​​such as:

  2 9 11 22 

Any help?

Answer 1

You can use SUM in the companion subquery or CROSS APPLY , like this

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 used SQL Server 2012 or higher, SUM() with an Over() clause could be used as follows.

 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 to use a recursive CTE. Something like that.

Note. This method is based on Roman Pekar's answer in this thread. Calculating Total Running in SQL Server . Based on his observations, this will work better than the one associated with the subquery, and CROSS APPLY as

 ;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)