Win a copy of The Little Book of Impediments (e-book only) this week in the Agile and Other Processes forum!
  • Post Reply
  • Bookmark Topic Watch Topic
  • New Topic

SQL statment - issues with SUM

 
Peter Primrose
Ranch Hand
Posts: 755
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
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
Ranch Hand
Posts: 755
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
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
author & internet detective
Marshal
Posts: 35279
384
Eclipse IDE Java VI Editor
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
Peter,
You can do something like this:
 
Peter Primrose
Ranch Hand
Posts: 755
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
excellent. Thank you!
 
  • Post Reply
  • Bookmark Topic Watch Topic
  • New Topic