Win a copy of Java 9 Modularity: Patterns and Practices for Developing Maintainable Applications this week in the Java 9 forum!
  • Post Reply Bookmark Topic Watch Topic
  • New Topic

SQL statment - issues with SUM  RSS feed

 
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
Sheriff
Posts: 37382
531
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!
 
It is sorta covered in the JavaRanch Style Guide.
  • Post Reply Bookmark Topic Watch Topic
  • New Topic
Boost this thread!