CODEWITHSUNDEEP
sql-serversumsql-server-2014
Mohammad Galouz Lee
Mohammad Galouz Lee

Reputation: 243

SQL Server sum column base on id

I have table and I want result like this:

id      ItemId      Quantity   Sum
 1         1            2       2
 2         1            1       3
 3         1            1       4
 4         2            3       3
 4         2            1       4
 5         3            6       6

where id is primary key and ItemId is foreign key and Quantity is integer column.

I want that "sum" column sum quantity base on itemId. I used group by but it does not give me what I want. With group by I get this result:

id      ItemId      Quantity    Sum
 1         1            2        4
 2         1            1        4
 3         1            1        4
 .         .            .        .
 .         .            .        .

The "sum" column is same for all related rows.

I also used this code:

select it1.id, it1.ItemId, it1.Quantity ,

(select sum(it2.Quantity) from InventoryTransactions it2 where it2.ItemId = it1.ItemId and it2.id <= it1.Id) as Sum

 from InventoryTransactions it1
 order by it1.ItemId , it1.Id

This code gives me what I want but it takes too long for a large number of records, for example for one million records. It takes more than two minute to execute

What is the best way?

Upvotes: 1

Views: 513

Answers (1)

Mihai
Mihai

Reputation: 26804

In sql server >= 2012 you can do a rolling sum easier

select it1.id, it1.ItemId, it1.Quantity,
sum(Quantity) over(partition by itemid order by Id rows unbounded preceding) as Sum
from InventoryTransactions it1
 order by it1.ItemId , it1.Id

Upvotes: 3

Related Questions