Jump to content


Photo

Having Trouble w/ Complex Query that uses SUM and JOINS 3 Tables


  • Please log in to reply
5 replies to this topic

#1 nsstudio

nsstudio
  • New Members
  • Pip
  • Newbie
  • 5 posts

Posted 27 April 2006 - 05:38 AM

I'm having trouble getting a query to work, and would love some help... Basically, I'm trying to build a PHP "Accounts Receivables" report to see what clients owe money, and how much. I have three tables: "clients" with the client's ID, name, address, etc; "projects" which has a field called price, among others, and contains multiple projects per client; and "payments" which keeps track of all client payments and has an amount field, amont others.

I'm trying to have a report that lists the client ID, name, total price, and total payments. Then I'll have a separate PHP variable to subtract the total payments from the total price and display the balance.

Here is my current code... It works fine if I remove the third JOIN table and the SUM(payments.amount), but as soon as I add those, it multiplies the sum values of both price and payments by the number of returned rows, and all the numbers are inaccurate. For example, if the SUM(projects.price) "should" be $500 and there were two payments made for $250 each, it will list both sums as $1000.

Does anyone have any ideas? Below is my current query:


SELECT clients.id, clients.name, SUM(projects.price), SUM(payments.amount) FROM clients LEFT JOIN projects ON clients.id=projects.client_id LEFT JOIN payments ON projects.client_id=payments.client_id GROUP BY clients.id

#2 koencalliauw

koencalliauw
  • Members
  • PipPipPip
  • Advanced Member
  • 36 posts

Posted 27 April 2006 - 06:34 AM

try experimenting with the DISTINCT keyword in MySQL (select distinct client.id, ...)

Koen

#3 nsstudio

nsstudio
  • New Members
  • Pip
  • Newbie
  • 5 posts

Posted 27 April 2006 - 04:52 PM

Thanks! I tried that, but no luck... I'm wondering if it has to do something with the type of join, or if I have to do a nested query... Anyone have any ideas?

#4 Barand

Barand
  • Moderators
  • Sen . ( ile || sei )
  • 18,017 posts

Posted 28 April 2006 - 01:24 PM

If you have

project
client   |   price
   1     |    500


payment
client  |  amount
  1     |    250
  1     |    250


then the joined result is
client |  price  |  amount
  1    |   500   |   250
  1    |   500   |   250

So when you sum the price it doubles (or trebles if 3 payments etc)

So you need a subquery.
If you are still using mysql_ functions, STOP! Use mysqli_ or PDO. The longer you leave it the more you will have to rewrite.

Donations gratefully received






moon.png

|baaGrid| easy data tables - and more
|baaChart| easy line, column and pie charts

#5 nsstudio

nsstudio
  • New Members
  • Pip
  • Newbie
  • 5 posts

Posted 28 April 2006 - 03:56 PM

That's what I thought... So now my question is, can anyone help me with the subquery? I've seen them done, but never using a SUM field, and am not quite sure what order to put the queries in. Thanks again for all your help!

#6 Barand

Barand
  • Moderators
  • Sen . ( ile || sei )
  • 18,017 posts

Posted 28 April 2006 - 07:01 PM

This should work even if client has multiple projects
SELECT client.id, client.name, SUM(project.price) AS tot_price, subq.tot_pay
FROM (client INNER JOIN project ON client.id = project.clientid)
INNER JOIN  (SELECT clientid, SUM(amount) as tot_pay FROM payment GROUP BY clientid) as subq
ON client.id = subq.clientid
GROUP BY client.id, client.name, subq.tot_pay;

If you are still using mysql_ functions, STOP! Use mysqli_ or PDO. The longer you leave it the more you will have to rewrite.

Donations gratefully received






moon.png

|baaGrid| easy data tables - and more
|baaChart| easy line, column and pie charts




0 user(s) are reading this topic

0 members, 0 guests, 0 anonymous users