Merge rows in SQL with specific logic

Question

I have the following table:

ProductCategory	Qty	Price
Wood	2	40
Metal	2	20
Glass	2	40
Other	2	30
Misc	3	10
Extra	5	20

I would like to merge Other, Misc and Extra categories as "Other" category. Qty and Price can have sum of Other, Misc and Extra categories.

Product Category	Qty	Price
Wood	2	40
Metal	2	20
Glass	2	40
Extra	10 (i.e. 2+3+5)	60 (i.e. 30 + 10 + 20)

One of the ways is to:

-- Create temp table to hold sum of Other, Misc, Extra

DECLARE @Qty AS INT, @Price AS INT

SELECT @Qty = Sum(Qty), @Price = Sum(Price) 
FROM Product 
WHERE ProductCategory IN ('Other', 'Extra', 'Misc')

DELETE FROM Product 
WHERE ProductCategory IN ('Other', 'Extra', 'Misc')

INSERT INTO Product (ProductCategory, Qty, Price) 
VALUES ('Extra', @Qty , @Price)

What is the easiest way to do this using SQL?

Answer 1

Using an OUTPUT to a temp table

declare @tmp table(Qty int, Price int);

delete tbl
  output deleted.qty, deleted.price into @tmp (Qty, Price)
where ProductCategory in ('Misc','Other');

update t
  set Qty = t.Qty + u.qty , Price = t.Price + u.Price
from tbl t
join (select sum(Qty) qty, sum(Price) Price
      from @tmp) u
      on t.ProductCategory = 'Extra';

db<>fiddle