help with group query
DBAsupport.com Forums - Powered by vBulletin
Results 1 to 3 of 3

Thread: help with group query

  1. #1
    Join Date
    Sep 2001
    Location
    Mexico
    Posts
    93

    Question help with group query. Details and group info in the same result

    Hi everyone,
    I need to make a query that show me details and grouping information at the same time.

    This is my original table :

    id who money
    1 Henry 10
    2 John 5
    3 John 10
    4 Mario 20

    And I want to obtain this information:

    id who money
    1 Henry 10
    Sum 10 <-- Groupping
    2 John 5
    3 John 10
    Sum 15 <-- Groupping
    4 Mario 20
    Sum 20 <-- Groupping

    I'm thinking that I can use a group by - rollup sentece.
    But I don't know how to do it.
    Any ideas?

    Thanks in advance for your help
    Regards
    Last edited by Turin; 10-31-2003 at 01:13 PM.

  2. #2
    Join Date
    Dec 2000
    Location
    Ljubljana, Slovenia
    Posts
    4,439
    Code:
    Connected to:
    Oracle9i Enterprise Edition Release 9.0.1.1.1 - Producti
    With the Partitioning option
    JServer Release 9.0.1.1.1 - Production
    
    SQL> SELECT * FROM your_table;
    
            ID WHO                       MONEY
    ---------- -------------------- ----------
             1 Henry                        10
             2 John                          5
             3 John                         10
             4 Mario                        20
    
    SQL> SELECT id, who, SUM(money) FROM your_table
      2  GROUP BY rollup(who, id);
    
            ID WHO                  SUM(MONEY)
    ---------- -------------------- ----------
             1 Henry                        10
               Henry                        10
             2 John                          5
             3 John                         10
               John                         15
             4 Mario                        20
               Mario                        20
                                            45
    
    8 rows selected.
    Those rows in bold are subtotals (the last one is grandtotal) that were added by ROLLUP.
    Jurij Modic
    ASCII a stupid question, get a stupid ANSI
    24 hours in a day .... 24 beer in a case .... coincidence?

  3. #3
    Join Date
    Sep 2001
    Location
    Mexico
    Posts
    93
    Great!! You're a master!
    You have saved my life

    Thank you very much

    Regards.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  



Click Here to Expand Forum to Full Width