CODEWITHSUNDEEP
sqlsql-server
Muhamad Jafarnejad
Muhamad Jafarnejad

Reputation: 2601

How to merge two records and sum a column too in SQL Server

There is a simple table with two columns. Col1 is like an identifier.

I want to SUM the quantity of rows with the same Col1 value. And UPDATE the current table.

Current table:

Col1 | Quantity
-----+----------
12   | 3
15   | 7
12   | 2

The UPDATED table I need is to SUM the quantity of two rows with Col1=12 (e.g .. WHERE Col1=12 ...) and Merge Them as one row:

Col1 | Quantity
-----+----------
12   | 5
15   | 7

How is it possible in a SQL Server query?

Please note that I need to update the table. Not just select rows.

Upvotes: 0

Views: 1707

Answers (2)

LukStorms
LukStorms

Reputation: 29677

You can use the MERGE statement for this.
Like with the UPDATE statement it's possible to combine with a Common-Table-Expression.
And in the CTE the window functions can be used. 

;WITH CTE AS
(
  SELECT *,
   ROW_NUMBER() OVER (partition by Col1 order by Quantity) as rn,
   SUM(Quantity) OVER (partition by Col1) as TotalQuantity,
   COUNT(*) OVER (partition by Col1) as Cnt
  FROM TestTable
)
MERGE (SELECT * FROM CTE WHERE cnt > 1) target
USING (SELECT * FROM CTE WHERE cnt > 1 AND rn = 1) src
ON (src.Col1 = target.Col1 AND src.rn = target.rn)
WHEN NOT MATCHED BY SOURCE 
    THEN DELETE
WHEN MATCHED
    THEN UPDATE SET target.Quantity = source.TotalQuantity;

A test on rexrester here

Such statement would however update all records of the table each time you re-run it. Even when the dups were already deleted.
But with a few tweaks it'll become a MERGE query that doesn't update those that have no dups.

;WITH CTE AS
(
    SELECT *
    FROM
    (
        SELECT Col1, Quantity,
        ROW_NUMBER() OVER (partition by Col1 order by Quantity DESC) as rn,
        COUNT(*) OVER (partition by Col1) as cnt,
        SUM(Quantity) OVER (partition by Col1) as TotalQuantity
        FROM TestTable
    ) q
    WHERE cnt > 1
)
MERGE CTE t
USING (SELECT * FROM CTE WHERE rn = 1) src
ON (src.Col1 = t.Col1 AND src.rn = t.rn)
WHEN NOT MATCHED BY SOURCE
    THEN DELETE
WHEN MATCHED
    THEN UPDATE SET t.Quantity = src.TotalQuantity;

Upvotes: 1

Fahmi
Fahmi

Reputation: 37483

use sum() aggregation with group by

with cte as 
(
select col1,quantity,row_number() over(partition by col1 order by quantity) as rn
from tablename
)

    update a set a.quantity=b.qty
    from cte a 
    inner join
    (select col1, sum(quantity) as qty
        from tablename
        group by col1
    )b on a.col1=b.col1 where rn=1

delete a from tablename a
join
(
select col1,quantity,row_number() over(partition by col1 order by quantity) as rn
    from tablename
)b on a.col1=b.col1 and rn>2

Upvotes: 2

Related Questions