sql deleting duplicate row

I have a table in SQL

Id   owner_id   amount
1     100        1000
2     101        2000
3     100        3000
4     104        800
5     100        1200

i want only one owner_id i don’t want 100 multiple times, but i want amount of all owner_id 100 i,e that amount should be added(i,e. 1000+3000+12000) if i delete duplicate Owner_id row. how to do it

And one more issue that owner_id from another table, how to get Owner name from another table. How to add join to get name of the owner

Answer

try this :

-- Acumulate all the amount to be able to do the cleanup
UPDATE table SET amount = sumAmount
FROM table t
JOIN (SELECT owner_id, SUM(amount) sumAmount
FROM table
GROUP BY owner_id) x ON x.owner_id = t.owner_id;

-- Delete duplicated data
WITH CTE AS (
    SELECT *, ROW_NUMBER() OVER (PARTITION BY owner_id, amount ORDER BY Id) row
    FROM table)
DELETE CTE WHERE row <> 1