Combining duplicate records

Question

Post reply

Combining duplicate records

rightfield

SSCrazy

Points: 2418
More actions
December 14, 2007 at 9:11 am

#185006

Hi,

I have a table (actually a few) that was corrupted during a merge. There are two columns (ref_no and item_no) that make each record unique but I now have three records (sometimes only two). To combine the the records into one, I need to get the sum of three separate amount columns and put each sum into the combined record's amount columns.

Rec1: ref_no, item_no, ee_amt_1, er_amt_1, retained_1

Rec2: ref_no, item_no, ee_amt_2, er_amt_2, retained_2

Rec3: ref_no, item_no, ee_amt_3, er_amt_3, retained_3

Combined Rec: ref_no,

item_no,

sum(ee_amt_1 + ee_amt_2 + ee_amt_3) as ee_amt,

sum(er_amt_1 + er_amt_2 + er_amt_3) as er_amt,

sum(retained_1 + retained_2 + retained_3) as retained

This doesn't look to complicated from a programming perspective but I am new to SQL programming and not used to looking at and manipulating data based on result sets. What would be the best way to solve this problem?

Thank you.

Warm regards,

Viewing 5 posts - 1 through 4 (of 4 total)

You must be logged in to reply to this topic. Login to reply

Jeff Moden SSC Guru Points: 1000539 More actions · Answer 1

Just trying to make sure...

1. Are the 2/3 detail rows in a different table than the "combined" table?

2. If so, are there rows already in the "combined" table for each ref_no and item_no pair that can occur in the detail table?

3. Does the "combined" table have any rows in it or are we trying to regenerate the combined table altogether?

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.
"Change is inevitable... change for the better is not".

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)
Intro to Tally Tables and Functions

Matt Miller (4) SSC Guru Points: 124210 More actions · Answer 2

to follow on to Jeff's questions:

- Do you want to STORE just one instance of the combined row, or do you want to DISPLAY the combined "table" even if the underlying data might have more than one row?

Didn't know you were going to get quizzed, huh?

----------------------------------------------------------------------------------
Your lack of planning does not constitute an emergency on my part...unless you're my manager...or a director and above...or a really loud-spoken end-user..All right - what was my emergency again?

rightfield SSCrazy Points: 2418 More actions · Answer 3

Hi Matt and Jeff,

I don't have a combined table but can make one if necessary. I don't need to worry about displaying because the problem is apparently already invisible to the appliation that uses the table. What I would like to end up with is the original table without any duplicates and the columns summed. Are you thinking it would be easiest to make a combined table and then drop the corrupted one and substitute the combined table?

Warm regards,

Matt Miller (4) SSC Guru Points: 124210 More actions · Answer 4

then try something like :



select

     ref_no,

     item_no,

     sum(ee_amt) as ee_amount,

     sum(er_amt) as er_amt,

     sum(retained) as retained

into Combined_table

from mytable

group by ref_no, item_no

You now have a table with the combined amounts. You could simply drop the old table and rename the combined to it (assuming you don't have anything else tied into it). Or - truncate the old table and put these records back into mytable.

----------------------------------------------------------------------------------
Your lack of planning does not constitute an emergency on my part...unless you're my manager...or a director and above...or a really loud-spoken end-user..All right - what was my emergency again?