# Thread: Computing sub-totals for each group

1. Registered User
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. Registered User
Join Date
Jan 2003
Posts
4,310
I think this is what you want:

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

Andy

3. Registered User
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. Registered User
Join Date
Jan 2003
Posts
4,310
If your table is like this:

--------------------------------
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:
--------------------------------
1 1 25 135
1 2 135 25
1 3 6 -
2 1 100 100

Andy

5. Super Moderator
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

#### Posting Permissions

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