CODEWITHSUNDEEP
sqlsql-servergroup-bysql-update
Time Attendance System
Time Attendance System

Reputation: 319

How to sum and subtract values using update with group by Sql query?

MyTable:

item_name   Qty     item_area
Belts        2        India
Shoes        20       US
T-Shirt      10       India
T-Shirt      12       US
T-Shirt      25       US

I get this by select group by query

SELECT item_name, Sum(item_qty) as Qty, item_area
FROM dbo.item_stocks
group by item_area, item_name

and my output is below:

item_name   Qty     item_area
    Belts        2        India
    Shoes        20       US
    T-Shirt      10       India
    T-Shirt      37       US

Now i need to subtract and update .How can i do this ? For eg. i want to subtract 5 T-Shirt ,it will update in MyTable?

T-shirt=37-5=32

how can i update in MyTable?

Upvotes: 0

Views: 527

Answers (2)

Ravi
Ravi

Reputation: 1172

just add a column which denotes in/Out, say INOUT

don't update row, just add new row for the stock sale.

Here,

INOUT = 1 denotes IN and 2 denotes OUT

table structure like

item_name   Qty     item_area    INOUT
Belts        2        India        1
Shoes        20       US           1
T-Shirt      10       India        1
T-Shirt      12       US           1
T-Shirt      25       US           1
T-Shirt       5       US           2

now your query looks like that

SELECT item_name, 
  Sum(case when INOUT = 1 then item_qty else (0-item_qty) end) as Qty, 
  item_area
FROM dbo.item_stocks
group by item_area, item_name

Upvotes: 2

D-Shih
D-Shih

Reputation: 46229

You can try to use ROW_NUMBER window function to choose any one row to subtract.

;WITH CTE AS (
   SELECT *,ROW_NUMBER() over(PARTITION BY item_name,item_area order by Qty desc)rn 
   FROM item_stocks
)

update CTE
set Qty = Qty - 5
where rn = 1 and  item_name = 'T-Shirt' and item_area = 'US'

SELECT item_name, Sum(Qty) as Qty, item_area
FROM item_stocks
group by item_area, item_name

Upvotes: 2

Related Questions