Results 1 to 5 of 5
  1. #1
    Join Date
    Jan 2005
    Posts
    18

    Unanswered: Computing sub-totals for each group

    Hi Friends,
    I've a query like this:
    Select user_id,table_no,currency,sum(amount) as amount from users group by user_id,table_no,currency.

    Userid tableno CAD amount USD amount
    -1-----10---------- 20 ---------- 20
    -1-----20------------------------ 10
    -1-----30-----------10----------- 20

    I have two currencies,CAD(canadian dollars) and USD(US dollars) and one user can sit on different tables.
    Is there a way by which I can get the sub-total of each user for both USD and CAD for all the tables he is
    playing In.Or,I've to use java and compute the sum,like this:

    Userid tableno CAD amount USD amount
    -1-----10---------- 20 ---------- 20
    -1-----20------------------------ 10
    -1-----30-----------10----------- 20
    --------------------30----------- 50

    Hope anyone of you would help me out ..thanks

  2. #2
    Join Date
    Jan 2003
    Posts
    4,292
    Provided Answers: 5
    I think this is what you want:

    SELECT userid,tableno,sum(CAD_AMOUNT),sum(USD_AMOUNT) from users group by userid,tableno

    Andy

  3. #3
    Join Date
    Jan 2005
    Posts
    18

    Thanks andy

    Thanks andy,but that wud give me a total of USD and CAD for all the users.But i need the total for each user.I know in Oracle ,this is the way to do it:
    sum(amount) on user_id.
    This will generate a new row whenever the user_id changes,and compute the sum(no matter in USD or CAD,i am just showin givin an example) for the particular user before moving on to the next user.
    Can you help me now?

  4. #4
    Join Date
    Jan 2003
    Posts
    4,292
    Provided Answers: 5
    If your table is like this:

    userid tableno CAD USD
    --------------------------------
    1 1 - 100
    1 1 25 35
    1 2 100 25
    1 2 35 --
    1 3 6 --
    2 1 100 100

    Using the query I sent should give you:
    userid tableno CAD USD
    --------------------------------
    1 1 25 135
    1 2 135 25
    1 3 6 -
    2 1 100 100

    Andy

  5. #5
    Join Date
    Aug 2001
    Location
    UK
    Posts
    4,650
    Look at the OLAP Functions ROLLUP and CUBE ..

    The SQL Cookbook (link to which can be found in the home page of www.db2click.com ) gives some excellent examples of the usage of these functions

    Cheers
    Sathyaram
    Visit the new-look IDUG Website , register to gain access to the excellent content.

Posting Permissions

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