CODEWITHSUNDEEP
mysqljoinsum
Barry Chapman
Barry Chapman

Reputation: 6780

MySQL: How do I select two fields from table, and total of amount columns in two related tables?

I have three tables:

Project PurchaseA PurchaseB

Schema of Project:

id | user_id | title | description | create_date

Schema of PurchaseA:

id | project_id | amount | create_date

Schema of PurchaseB:

id | project_id | amount | create_date

Project has a bunch of unique rows detailing different projects. Each of these projects has many rows in both PurchaseA and PurchaseB. What I want to do is select all of the rows from Project with the fields id and title, but also the SUM of both the total of the related records in PurchaseA and the total of the related records in PurchaseB.

I can get it working sort of independently of each other, but when I have two joins, the numbers end up being massively inflated.

Desired Result

21732 Test Project A          84274.48
21747 Test Project B          18237.47
34714 Test Project C         183747.18

Where the column with the FLOATS is the sum of the related rows from PurchaseA and PurchaseB.

Edit

Here is my current SQL:

select 
    projects.id,
    projects.title,
    COALESCE(SUM(PurchaseA.amount), 0) as pledgetotal,
    COALESCE(SUM(PurchaseB.amount), 0) as purchasetotal
from
    projects
        left join
    PurchaseA ON (PurchaseA.project_id = projects.id)
        left join
    PurchaseB ON (PurchaseB.project_id = projects.id)
        left join

WHERE
    projects.title IS NOT NULL

    projects.end_date < NOW()
group by projects.id

Test Data

Projects:

id          title           description         create_date

623         Test Project A  This is a test A    2013-01-01
624         Test Project B  This is a test B    2013-01-02


PurchaseA

id          project_id      amount              create_date
1           623             100                 2013-01-02
2           623             125                 2013-01-02
3           624             200                 2013-01-03
4           623             150                 2013-01-03
5           624             50                  2013-01-04

PurchaseB

id          project_id      amount              create_date
1           623             110                 2013-01-02
2           624             105                 2013-01-02
3           623             215                 2013-01-03
4           623             100                 2013-01-03
5           624             150                 2013-01-04

So as you can see, each project has multiple rows in each related table. I want to get the sums from each table, then add those together.

Upvotes: 1

Views: 2819

Answers (2)

Taryn
Taryn

Reputation: 247860

I believe part of your problem is from the one-to-many relationship that exists between the projects and the purchase. tables. My suggestion would be to move the sum() to subqueries:

select 
    p.id,
    p.title,
    a.pledgetotal,
    b.purchasetotal
from projects p
left join
(
  select project_id, sum(amount) pledgetotal
  from PurchaseA
  group by project_id
) a
  ON (a.project_id = p.id)
left join
( 
  select project_id, sum(amount) purchasetotal
  from PurchaseB
  group by project_id
) b
  ON (B.project_id = p.id)
WHERE p.title IS NOT NULL;

See SQL Fiddle with Demo

Upvotes: 1

MISJHA
MISJHA

Reputation: 1008

You can try this (not tested):

SELECT a.id, a.title, (pa.amount+pb.amount)  AS TotalPurchase FROM Project a
    LEFT JOIN ( SELECT project_id, SUM(amount) AS amount FROM PurchaseA GROUP BY project_id ) pa
    ON pa.project_id = a.id
    LEFT JOIN ( SELECT project_id, SUM(amount) AS amount FROM PurchaseB GROUP BY project_id ) pb
    ON pb.project_id = a.id

Upvotes: 0

Related Questions