Summing Columns

Question

This is my sample table

create table #t (id int,qty int)
insert into #t values(1,100),(2,200),(3,150),(4,50)

I am trying to get this

id        qty       total_qty
1   100 100
2   200 300
3   150 450
4   50  500

this is not working

select a.id,a.qty,sum(b.qty) as total_qty from #t

Answer 1

You want a cumulative sum. In SQL Server 2012, you can do this directly:

select a.id, a.qty, sum(a.qty) over (order by a.id) as total_qty
from #t a;

In earlier versions of SQL Server, you can do this with a correlated subquery or non-equijoin. Below is an example:

select t.id, t.qty,
       (select sum(t2.qty) from #t t2 where t2.id <= t.id) as total_qty
from #t t;

EDIT:

The expression sum(a.qty) over (order by a.id) is a window function that expresses a cumulative sum. The over is saying "do something for each row". What it does for each row is take the sum of a.qty. But, not just any sum, a sum that takes the ordering into account. The order by is what makes this a cumulative sum. (Without the order by clase, sum(a.qty) over () would return the sum of from all the rows, as an additional column in each row.)

Answer 2

Use a join !

select a.id,a.qty,sum(b.qty) as total_qty from #t a cross join #t b where b.id<=a.id
group by a.id,a.qty
order by a.id

see Demo