Complex Grouping in Oracle


DevX Home    Today's Headlines   Articles Archive   Tip Bank   Forums   

Results 1 to 2 of 2

Thread: Complex Grouping in Oracle

  1. #1
    Join Date
    Jul 2007
    Posts
    7

    Complex Grouping in Oracle

    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.

  2. #2
    Join Date
    Oct 2008
    Posts
    142

    -Divide and Conquer works a lot in SQL-

    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

Similar Threads

  1. setting up asp.net server
    By kylemcna in forum ASP.NET
    Replies: 5
    Last Post: 04-13-2007, 03:23 AM
  2. Complex number
    By burung in forum Java
    Replies: 3
    Last Post: 08-01-2005, 06:59 AM
  3. MTS, Oracle 7 /NT and remote databases
    By Eugene Chernyak in forum Enterprise
    Replies: 0
    Last Post: 07-27-2000, 10:35 AM

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
HTML5 Development Center
 
 
FAQ
Latest Articles
Java
.NET
XML
Database
Enterprise
Questions? Contact us.
C++
Web Development
Wireless
Latest Tips
Open Source


   Development Centers

   -- Android Development Center
   -- Cloud Development Project Center
   -- HTML5 Development Center
   -- Windows Mobile Development Center