Jump to content

Archived

This topic is now archived and is closed to further replies.

nsstudio

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

Recommended Posts

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

Share this post


Link to post
Share on other sites
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?

Share this post


Link to post
Share on other sites
If you have

[code]project
client   |   price
   1     |    500


payment
client  |  amount
  1     |    250
  1     |    250[/code]


then the joined result is
[code]client |  price  |  amount
  1    |   500   |   250
  1    |   500   |   250[/code]

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

So you need a subquery.

Share this post


Link to post
Share on other sites
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!

Share this post


Link to post
Share on other sites
This should work even if client has multiple projects
[code]
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;[/code]

Share this post


Link to post
Share on other sites

×

Important Information

We have placed cookies on your device to help make this website better. You can adjust your cookie settings, otherwise we'll assume you're okay to continue.