Forums Register Login

SQL statment - issues with SUM

+Pie Number of slices to send: Send
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 ]
+Pie Number of slices to send: Send
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
+Pie Number of slices to send: Send
Peter,
You can do something like this:
+Pie Number of slices to send: Send
excellent. Thank you!
We can walk to school together. And we can both read this tiny ad:
a bit of art, as a gift, the permaculture playing cards
https://gardener-gift.com


reply
reply
This thread has been viewed 897 times.
Similar Threads
sql problem - sum is Incorrect
XML file NOT well-formed in Tomcat 6 same file IS well-formed in Tomcat5.5
Whats Wrong in my Thread program
Contract money
Group & Sum Using XSLT
More...

All times above are in ranch (not your local) time.
The current ranch time is
Mar 29, 2024 06:01:46.