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

Question

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.

Please post the SQL query you have so far

Melanie
– Melanie

2013-05-03 19:47:16 +00:00
Commented May 3, 2013 at 19:47 — Melanie
– Melanie, Commented May 3, 2013 at 19:47
Can you post some sample data for each table?

Taryn
– Taryn

2013-05-03 19:57:28 +00:00
Commented May 3, 2013 at 19:57 — Taryn
– Taryn, Commented May 3, 2013 at 19:57

Taryn · Accepted Answer · 2013-05-03 20:11:56Z

1

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

answered May 3, 2013 at 20:11

Taryn

249k57 gold badges374 silver badges409 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

Barry Chapman Over a year ago

Works like a champ thumbs up

MISJHA · Accepted Answer · 2013-05-03 19:51:29Z

0

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

answered May 3, 2013 at 19:51

MISJHA

1,0085 silver badges13 bronze badges

3 Comments

Barry Chapman Over a year ago

That returns NULL for most of the rows

MISJHA Over a year ago

this is the same as the answer flagged as correct, I just didn't have any idea of what you wanted in the WHERE clause. ;)

Barry Chapman Over a year ago

when i ran yours, all the fields were NULL - his were not?

Collectives™ on Stack Overflow

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

2 Answers 2

1 Comment

3 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

1 Comment

3 Comments

Your Answer

Sign up or log in

Post as a guest

Related