CODEWITHSUNDEEP
sqlsql-server
Joey
Joey

Reputation: 281

how to accumulate values in SQL in one column based on another column?

I am trying to accumulate the results in the first table below containing distinct ItemID based on the quantities and multipliers in second table. I tried UPDATE records and

SET ItemCount = ItemCount + ((SELECT [Multiplier] FROM Table2 WHERE Table1.[ItemID] = Table2.[Item1ID]) * (SELECT [Item1Qty] FROM Table2 WHERE Table1.[ItemID] = Table2.[Item1ID])) + ((SELECT [Multiplier] FROM Table2 WHERE Table1.[ItemID] = Table2.[Item2ID]) * (SELECT [Item2Qty] FROM Table2 WHERE Table1.[ItemID] = Table2.[Item2ID]))

but this failed because the SELECT statement returns multiple values. What is the most efficient way to get the results shown in table3

ItemID      ItemCount
10001
10002
10003
10004

Item1ID     Item1Qty        Item2ID     Item2Qty     Multiplier
10001       1               10003        3            4
10004       3               10002        5            2
10003       3               10001        4            3

ItemID      ItemCount
10001       16
10002       10
10003       21
10004       6

Upvotes: 1

Views: 346

Answers (3)

sanatsathyan
sanatsathyan

Reputation: 1773

This will work :

Declare @Table as Table (Item1ID varchar(10),Item1Qty int,Item2ID varchar(10),Item2Qty int,Multiplier int)
Insert into @Table Values
('10001',1,'10003',3,4),
('10004',3,'10002',5,2),
('10003',3,'10001',4,3)

Declare @Table1 as Table (Item1ID varchar(10),ItemCount int)
Insert into @Table1 Values
('10001',NULL),
('10002',NULL),
('10003',NULL),
('10004',NULL)

;With T AS
(
Select Item1ID,Item1Qty,Multiplier from @Table
UNION
Select Item2ID,Item2Qty,Multiplier from @Table
)

Update T set T.ItemCount = S.ItemCount from @Table1 T
Inner Join (
Select Item1ID,SUM(Item1Qty*Multiplier) 'ItemCount' from T
Group by Item1ID
) S on S.Item1ID = T.Item1ID

Working sample

Upvotes: 1

Kasun
Kasun

Reputation: 346

UPDATE T3
SET T3.ItemCount = ISNULL(T3.ItemCount,0) + ((T2.Multiplier * T1.Item1Qty) + (T2.Multiplier * T2.Item2Qty))
FROM Table3 T3
INNER JOIN Table1 AS T1 ON T1.Item1ID = T3.ItemID
INNER JOIN Table2 AS T2 On T2.Item2ID = T1.Item1ID

Upvotes: 0

uzi
uzi

Reputation: 4146

One way is to use union all to count total quantity for each item. Then update table1

with cte as (
    select
        itemID, totalQty = sum(qty)
    from (
        select itemID, qty = item1Qty * Multiplier from table2
        union all select itemID, item2Qty * Multiplier from table2
    ) t
    group by itemID
)

update a
set b.ItemCount = b.qty
from
    table1 a
    join cte b on a.itemID = b.itemID

Upvotes: 2

Related Questions