SQL statment - issues with SUM

[Peter Primrose]

ok, here is the deal:

I have 2 tables: invoice & payments.

the invoice include all information the customer needs to pay (hotel, flights...) the payments shows all payments done by him.

So.... I am trying to get the SUM of all the invoice AND the SUM of all payments.
it works...but not correctly. It if there are 5 arguments in the deposit and one payment it multiply the deposit 5 times (the amount of argumetns in the invoice).

My statment is this:

SELECT invoice.Customer_ID,
SUM( (perCustomer)* (timesCustomer)) as TotalInvoice,
SUM( paymentAmount) as TotalPayments,
( SUM(perCustomer)* SUM(timesCustomer)-SUM(paymentAmount) ) as Balance
FROM invoice, payment
WHERE invoice.Customer_ID = payment.Customer_ID
GROUP BY Customer_ID


any idea???
thanks
Peter

[Bear edit: improved title]
[ April 07, 2005: Message edited by: Bear Bibeault ]

[Peter Primrose]

Clarification:

how can i get the output below?

Table invoice

CustomerID invoiceAmount
105 30
105 20
105 5
94 8
94 22




Table payment

CustomerID paymentAmount
105 50
105 5
94 10



How can I get this info:


ID totalInvoiceAmount totalPaymentAmount
105 55 55
94 30 10

[Jeanne Boyarsky]

Peter,
You can do something like this:

  
select * from 
(select customerId, sum(invoiceAmount) from invoice group by customerId) a,
(select customerId, sum(paymentAmount) from payment group by customerId) b
where a.customerId = b.customerId

[Peter Primrose]

excellent. Thank you!