SQL Group by and update/delete row

Question

I have a SQL.Table

CREATE TABLE [dbo].[Stack_Example](
    [ID] [bigint] NOT NULL,
    [Product_ID] [bigint] NULL,
    [Quantity] [decimal](18, 2) NULL,
    [Price] [decimal](18, 2) NULL,
 CONSTRAINT [PK_Stack_Example] PRIMARY KEY CLUSTERED 
(
    [ID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]

With data like shown under:

INSERT [dbo].[Stack_Example] ([ID], [Product_ID], [Quantity], [Price]) VALUES (1, 25, CAST(55.00 AS Decimal(18, 2)), CAST(1000.00 AS Decimal(18, 2)))
GO
INSERT [dbo].[Stack_Example] ([ID], [Product_ID], [Quantity], [Price]) VALUES (2, 25, CAST(1.00 AS Decimal(18, 2)), CAST(1000.00 AS Decimal(18, 2)))
GO
INSERT [dbo].[Stack_Example] ([ID], [Product_ID], [Quantity], [Price]) VALUES (3, 26, CAST(1.00 AS Decimal(18, 2)), CAST(500.00 AS Decimal(18, 2)))
GO

So my problem here is i need to group those items by Price, Product_Id and SUM(Quantity).

There is need for Update/Delete query assuming my output window need to looks like

Its simple when you do a select query

select Product_ID,SUM(Quantity) AS Quantity,Price from Stack_Example
Group by Product_ID,Price

So at very begin what i need to do is to delete the row with id : 2 And update the row with id 1 set Quantity = Quantity + 1 ( Quantity of deleted row ).

So when i run the select query without grouping and summing i need to get the same output

UPDATE Stack_Example SET Quantity = (SELECT SUM(Quantity)
                                    FROM Stack_Example child 
                                    WHERE child.Product_ID = Stack_Example.Product_ID
                                    GROUP BY Product_ID)

DELETE FROM Stack_Example WHERE ID IN (SELECT TOP 1 ID 
                                        FROM Stack_Example 
                                        WHERE Product_Id IN ( (SELECT TOP 1 Product_ID 
                                                                    FROM Stack_Example
                                                                    GROUP BY Product_ID 
                                                                    HAVING COUNT(Product_ID)>1)))

Answer 1

First update your table with total amount of products with same price

with cte as (
    select
        Product_ID, Price, Quantity = sum(Quantity) 
    from
        Stack_Example
    group by Product_ID, Price
)

update s
set s.Quantity = c.Quantity
from
    Stack_Example s
    join cte c on s.Product_ID = c.Product_ID and s.Price = c.Price

Then run another script to delete duplicate rows

with cte as (
    select
        *, row_number() over (partition by Product_ID, Price order by Product_ID) rn
    from
        Stack_Example
)

delete from cte where rn > 1

Answer 2

I'm a bit unclear - but couldn't you just create a view with the aggrgates?

Anyhow..... Not sure why you would do this but...you could insert the aggregated values and then delete the older ones....as per below.

DECLARE @MaxId INT;
DECLARE @ProductId = 25;


--GET THE MAX ID OF PRODUCT IN QUESTION
SELECT @MaxId = MAX(ID) FROM Stack_Example WHERE Product_Id = @ProductId;


--INSERT THE AGGREGATE VALUES INTO TABLE
INSERT INTO Stack_Example
  SELECT @MaxId + 1, @ProductId, Price, SUM(Quantity)
  FROM Stack_Example
  WHERE 
    Product_Id = @ProductId
  GROUP BY 
    @MaxId + 1, [Product_ID], [Price];

--DELETE THE PRE-AGGREGATE VALUES
DELETE FROM Stack_Example WHERE ProductId = @ProductId and Id <= @MaxId;

Answer 3

Well, I used aggregates and additional temporary table to achieve that:

--group records and insert them into temporary table
select distinct
     Min(id) over (partition by product_id, price) [ID],
     Product_id,
     sum(Quantity) over (partition by product_id, price) [Quantity],
     Price
into #new_stack_example
from #Stack_Example

--delete old values
delete #Stack_example

--insert new values
insert into #Stack_example
select * from #new_stack_example

First SELECT statement reflects your logic:

So my problem here is i need to group those items by Price, Product_Id and SUM(Quantity).

But instead of grouping, I use window functions and then select distinct records.

Answer 4

Check this:

    UPDATE Stack_Example SET Quantity = a.Quantity
    from ( select Product_ID,sum(Quantity) Quantity  
    from Stack_Example Group by Product_ID) a
    where   Stack_Example.Product_ID=a.Product_ID   

    ;with cte as (
    select *,ROW_NUMBER () over (partition by product_id order by product_id) rn 
    from Stack_Example)

    delete from cte where rn > 1