SQL to merge records

[malaygal]

I have these records on my Oracle database version 10G

Area Account Jan Feb Mar Apr ...... Dec

LA111 A100 50 50 50 55 50
LA112 A100 2 2 2 3 2
LA112 A200 75 75 80 65 90
LA111 A300 20 25 20 25 30


How can I merge Area LA111 and LA112 together such that I will end up with these records:

LA111 A100 52 52 52 58 52
LA112 A100 0 0 0 0 0
LA111 A200 75 75 80 65 90
LA111 A300 20 25 20 25 30

Any help will be greatly appreciated.

 

[Turkbear]

Hi,
How does

[COLOR=blue]
LA112    A100         2         2         2         3             2
LA112   A200            75        75        80        65            90
[/color]

become

[COLOR=red]
LA112    A100         0         0            0         0              0
[/color]

by 'merging'?



To Paraphrase:"The Help you get is proportional to the Help you give.."

 

[Welshbird]

From your example it looks like you want to group by A100, take min(LA111) and sum the other columns.

I don't see that as a merge - but is that the idea?

Fee

The question should be [red]Is it worth trying to do?[/red] not [blue] Can it be done?[/blue]

 

[malaygal]

I don't konw if "merge" is the correct term.
LA112/A100 should be added to LA111/A100.
A record for LA111/A200 should be created so that I can move LA112/A200 to this new record.
After this is done, I should not have any data for LA112 at all.

 

[Welshbird]

How about something like:

select 
colA100, min(colLA111), sum(col3), sum(col4).....
from table
group by
colA100

Let us know how this works. Oh - this doesn't leave you with a LA112, A100 0 0 0 0 row though. Is this a requirement?

Fee

The question should be [red]Is it worth trying to do?[/red] not [blue] Can it be done?[/blue]

 

[malaygal]

Thanks for the reply:

Does it translate to this (with my sample column name)

select
Account, min(Area), sum(Jan), sum(Feb).....
from table
group by
Account

I have millions of records from this table (multiple acccounts and areas). I need to "merge" two areas together.
From the sample code you provided, where do I select these two areas to merge and the target area(the area that will eventually hold the merged data).

I am a newbie with SQL.

 

[Welshbird]

Well, in Oracle this is a 'group' rather than a merge, as you are 'grouping' things based on the account. You also seem to to using the lowest value of the area, and then adding together the other values.

So, you would use the code exactly as you have posted it.

If I were you (and assuming you have appropriate rights to do so) I would create a temp table that is smaller so you can play with it. Use something like

create table Test as
(select * from my_real_table where rownum < 501)
/

so you have a smaller version of your table holding only 500 rows to have a go with. This might make it easier to check/test your results.

(Sorry for delay in response - work been busy!)

Let us know how you get on.

Fee

The question should be [red]Is it worth trying to do?[/red] not [blue] Can it be done?[/blue]