Summing fields from multiple tables

[dan19]

I need an one step sql statement to retrieve a report
from 3 tables: CLIENT, TRANSACTIONS, PAYMENTS. The link
field is CLIENT_ID. For each client I need to sum all
the amounts found in the TRANSACTIONS and PAYMENTS tables.
It works fine if I (left) join the CLIENT with one of the other tables (using SUM and GROUP BY CLIENT_ID), but when I try to left join with the third table the sums are incorrect (actually it sums up the amounts found in TRANSACTIONS table as many times as the number of records in the PAYMENTS table). Can you help?

 

[PHV]

Why not simply this ?
select C.CLIENT_ID
, (select sum(TRANSACTION_AMOUNT) from TRANSACTIONS where CLIENT_ID=C.CLIENT_ID) TotalTransactions
, (select sum(PAYMENT_AMOUNT) from PAYMENTS where CLIENT_ID=C.CLIENT_ID) TotalPayments
from CLIENT C

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886