CODEWITHSUNDEEP
databaseoracle-databaseduplicates
Rahul
Rahul

Reputation: 95

Sum the amount and then delete the second row when its duplicate in Oracle

I am trying to do below two things for this table.

enter image description here

  1. There are 3 Unique row if we check by EMP_Id, Year and Type. I need to add sum of Bonus_Amt only when it is duplicate. For ex, For EMP_ID = 1708301 Bonus_AMT should be = 22196 + 3036. It should not do anything for EMP_ID = 642416.

  2. Once the sum is done delete the second row but do check carefully that Bonus_Amt should be less then other one in duplicate record.

The table record should be like below when step 1 and step 2 is performed.

enter image description here

I did try something like below for Point 1 but this is giving me row number 2 and not row number 1. If i change t.rn = 1 then it is picking all records even the one which is not duplicate.

SELECT t.*
FROM (SELECT o.*, ROW_NUMBER() OVER (PARTITION BY o.EMP_Id ORDER BY o.Year) rn
     FROM Emp_Stat o where o.Year='2018' AND o.Type= 'Check' 
     ) t 
WHERE t.rn  > 1

Here UniqueId does not matter. The catch is that only the record which is duplicate should be sum and then the one which is not added should be deleted. Also there is a column lst_update_time which should update to sysdate only for the duplicate row and not for any other record.

Upvotes: 0

Views: 623

Answers (2)

Boneist
Boneist

Reputation: 23588

You can do this in a single MERGE statement, using both update and delete in the when matched clause, like so:

merge into t1 tgt
  using (select unique_id,
                emp_id,
                sum(bonus_amt) over (partition by emp_id, year, type) new_bonus_amt,
                year,
                type,
                count(*) over (partition by emp_id, year, type) grp_count,
                row_number() over (partition by emp_id, year, type order by bonus_amt desc) rn
         from   t1) src
  on (tgt.unique_id = src.unique_id and src.grp_count > 1)
when matched then
   update set tgt.bonus_amt = src.new_bonus_amt,
              tgt.last_update_time = sysdate
   delete where rn != 1;

Demo DBFiddle

This works by finding the sum of the bonus_amt for each group (doesn't matter if the group has a single row or multiple) and identifying the first row in the group (that is, the one with the highest bonus_amt), so we know which row to keep.

Then, the we use that source set of data in the merge statement to update the bonus_amt of every row (you need to update every row, otherwise the delete won't "see" the un-updated rows) before deleting all but the first row in each group.

Upvotes: 2

evgeniya makarova
evgeniya makarova

Reputation: 11

I like the answer with merge statement; Just wanted to suggest more transparent solution :

  1. Start
  2. Select sum grouped by the UK (as in that merge statement) and insert results into the auxiliary table. - You will have one record per UK
  3. Delete ALL records from the base table
  4. Insert into base table from aux .table
  5. commit work;

Upvotes: 0

Related Questions