Complex Grouping in Oracle

[kuul13]

Hi

I have two tables, ACCOUNTS and GROUPS. ACCOUNTS table store account level information with amount. And one person can have multiple accounts. GROUPS table store the account group information. One group can have multiple accounts of the sam person.
Account Table
ActNo, Name, Amount, ActName
11111, JEFF, 100.00, Funds
11112, JEFF, 200.00, Funds
11113, JEFF, 200.00, Funds
11114, ANNA, 120.00, Sal
11115, GARY, 232.00, Sal
11116, GARY, 122.00, Sal
Group Table
GrpId, ActNo
100 , 11111
100 , 11112
100 , 11113
101 , 11114
102 , 11115
102 , 11116
I want to get an output to show the total amount for each group with latest/maximum account information. For Example:
GrpID, ActNo, Name, TotAmnt, ActName
100 , 11113, JEFF, 500.00 , Funds
101 , 11114, ANNA, 120.00 , Sal
102 , 11116, GARY, 354.00 , Sal

I am not sure how to create a single single oracle query. Please help. Thanks in advance.

[tkorsano]

Hi there,

Maybe part of the solution you are looking for is close to:

(You need to create a #t1 temp table fot this)

insert into #t1
select g.grpid gruoup_id, sum(a.amount) total_amount, max(a.amount) maximum_amount
from groups g, account a
where a.accountno = g.accountno
group by g.grpid

This will give you group id, total amount and maximum amount.
You still require acctno, funds and acctname, so I will put this last sql statement in a temp table, that could be called #t1, and later

select t.group_id a.acctno, a.acctname, t.total_amount, a.funds
from #t1 t, account a
where a.acctno = t.acctno

I hope this gives you an idea on how to accomplish what you are looking for.

Tonci Korsano