title: SQL Group by and update/delete row
body: <p>I have a SQL.Table</p>
  
  <pre><code>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]
  </code></pre>
  
  <p>With data like shown under:</p>
  
  <pre><code>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
  </code></pre>
  
  <p>So my problem here is i need to group those items by Price, Product_Id and SUM(Quantity).</p>
  
  <p>There is need for Update/Delete query assuming my output window need to looks like</p>
  
  <p><a href="https://i.stack.imgur.com/HpZ0L.png" rel="nofollow noreferrer"><img src="https://i.stack.imgur.com/HpZ0L.png" alt="enter image description here"></a></p>
  
  <p>Its simple when you do a select query</p>
  
  <pre><code>select Product_ID,SUM(Quantity) AS Quantity,Price from Stack_Example
  Group by Product_ID,Price
  </code></pre>
  
  <p>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 ).</p>
  
  <p>So when i run the select query without grouping and summing i need to get the same output</p>
  
  <pre><code>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)&gt;1)))
  </code></pre>