Jump to content

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


nsstudio

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
Link to comment
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.
Link to comment
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]
Link to comment
Share on other sites

This thread is more than a year old. Please don't revive it unless you have something important to add.

Join the conversation

You can post now and register later. If you have an account, sign in now to post with your account.

Guest
Reply to this topic...

×   Pasted as rich text.   Restore formatting

  Only 75 emoji are allowed.

×   Your link has been automatically embedded.   Display as a link instead

×   Your previous content has been restored.   Clear editor

×   You cannot paste images directly. Upload or insert images from URL.

×
×
  • Create New...

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.