SQL beginner need help

[Julien Martin]

Hello,

I have to deal with a single table which is as follows:

amountdebit/creditclient
30dA
40cA
20dB
10cB
5cB
2dA

I want to end up with this:

debitcreditclient 
3240A
2015B

Basically what I have done is a sum on the amounts of the same kind (d/c) and a group by on the client.

I have no idea on what SQL could produce the above result.

Can anyone help?

Thanks in advance,

Julien.

[Raghavan Muthu]

Have you tried executing the query? Just try it and see what it produces!

[Julien Martin]

I haven't got a clue what the query should look like. Can you give me a hint please?
Julien.

[Raghavan Muthu]

try using inline views through which you can achieve.

[Raghavan Muthu]

Try getting the sum of debit, credit individually in the form of inline view and then try to include them in a single select (from respective inline views).
[ June 22, 2007: Message edited by: Raghavan Muthu ]

[Edisandro Bessa]

The SQL below works for both Sybase and MySQL versions.

If you use another database just change it according to your needs.

select 
        client, 
        credit = sum(case credit_type when 'c' then amount 
                                               else 0 
                     end), 
        debit = sum(case credit_type when 'd' then amount 
                                              else 0 
                     end),
from YOURTABLE
group by client

[ June 22, 2007: Message edited by: Edisandro Bessa ]

[Julien Martin]

Thanks Edisandro,

I adapted your query as follows so that it work with DB2:

select 
(case debit_credit when 'd' then sum(amount) else 0 end ) as sum_debit, 
(case debit_credit when 'c' then sum(amount) else 0 end ) as sum_credit,  
client 
from test group by client, debit_credit

which gives me this:

SUM_DEBITSUM_CREDITCLIENT
015B
200B
040A
320A

whereas I would like this:

SUM_DEBITSUM_CREDITCLIENT
2015B
3240A

Any idea what I am missing?

Julien.

[Julien Martin]

I found the solution:

select sum(sum_debit) as tot_debit, sum(sum_credit) as tot_credit, client
from (select
(case debit_credit when 'd' then sum(amount) else 0 end ) as sum_debit,
(case debit_credit when 'c' then sum(amount) else 0 end ) as sum_credit,
client
from
test
group by client, debit_credit) as t
group by client