title: How to consolidate two sql server tables using Group By clause?
body: <p>I have two tables TableA and TableB as follows:</p>
  
  <p>TableA:</p>
  
  <pre><code>     ItemID          Qty           Rate
      --------        -----         --------
         1             10            100.00
         2             20            150.00
  </code></pre>
  
  <p>TableB:</p>
  
  <pre><code>     ItemID          Qty           Rate
      --------        -----         -------
         1              5            150.00
         3              10           200.00
         3              20           400.00
  </code></pre>
  
  <p>Now I want to consolidate these two tables. My desired result needs to be as follows:</p>
  
  <p>Result TableA:</p>
  
  <pre><code>     ItemID           Qty           Rate
      --------         -----         -------
         1              15            150.00
         2              20            150.00
         3              30            400.00
  </code></pre>
  
  <p>I tried the following Insert Select statement, But it does not give the desired result.</p>
  
  <pre><code>INSERT INTO TableA
  (
       ItemID,
       Qty,
       Rate               
  )
  SELECT 
      ItemID, 
      SUM(Qty), 
      MAX(Rate)
  FROM 
      TableB
  GROUP BY 
      ItemID
  </code></pre>
  
  <p>But it gives the result as follows:</p>
  
  <pre><code>     ItemID          Qty           Rate
      --------        -----         --------
         1             10            100.00
         2             20            150.00
         1              5            150.00
         3              30           400.00
  </code></pre>
  
  <p>How to achieve my desired result?</p>
  
  <p>I tried like this:</p>
  
  <pre><code> MERGE PUR_PODetail AS Target
                  USING (
                      SELECT 
                          @POID,
                          ItemID,
                          SUM(POQuantity),
                          MAX(UnitRate),
                          1,
                          CASE WHEN D1 = '' THEN NULL ELSE D1 END D1,
                          CASE WHEN D2 = '' THEN NULL ELSE D2 END D2,
                          CASE WHEN D3 = '' THEN NULL ELSE D3 END D3,
                          CASE WHEN RandomDimension = '' THEN NULL ELSE RandomDimension END RandomDimension,
                          0
                      FROM 
                          @Detail
                      GROUP BY 
                          ItemID, D1, D2, D3, RandomDimension
                      ) AS Source  ON (Target.ItemID = Source.ItemID) AND 
                      (ISNULL(Target.D1, -999) = ISNULL(Source.D1, -999)) AND 
                      (ISNULL(Target.D2, -999) = ISNULL(Source.D2, -999)) AND 
                      (ISNULL(Target.D3, -999) = ISNULL(Source.D3, -999)) AND
                      (ISNULL(Target.RandomDimension, -999) = ISNULL(Source.RandomDimension, -999))
                  WHEN MATCHED
                      THEN UPDATE SET 
                              Target.POQuantity = Target.POQuantity + Source.POQuantity,
                              Target.UnitRate = MAX(Source.UnitRate)
                  WHEN NOT MATCHED
                      INSERT
                          (
                              POID,
                              ItemID,
                              POQuantity,
                              UnitRate,
                              ItemStatusID,
                              D1,
                              D2,
                              D3,
                              RandomDimension,
                              EDInclusive_f
                          )
                      VALUES
                          (
                              @POID, 
                              Source.ItemID, 
                              Source.POQuantity, 
                              Source.UnitRate, 
                              1,
                              CASE WHEN Source.D1 = '' THEN NULL ELSE Source.D1 END D1,
                              CASE WHEN Source.D2 = '' THEN NULL ELSE Source.D2 END D2,
                              CASE WHEN Source.D3 = '' THEN NULL ELSE Source.D3 END D3,
                              CASE WHEN Source.RandomDimension = '' THEN NULL ELSE Source.RandomDimension END RandomDimension,
                              0
                          )
  </code></pre>
  
  <p>But it gives the following error.
  Please correct the error. I dont know what would be wrong here.</p>
  
  <p>Msg 102, Level 15, State 1, Procedure PUR_PurchaseOrder_IU, Line 936
  Incorrect syntax near 'MERGE'.
  Msg 156, Level 15, State 1, Procedure PUR_PurchaseOrder_IU, Line 953
  Incorrect syntax near the keyword 'AS'.</p>
  
  <p>But when I remove these merge statement from my stored procedure, it is executing...</p>