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

I think this is what you want:

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

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?

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

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