CODEWITHSUNDEEP
sqljoingroup-by
user1228983
user1228983

Reputation: 33

SQL: Joining 3 tables with sums, filtering on non-aggregated columns

I have the following tables:

    Plant:
ID      Name
1       Plant1
2       Plant2
......

   Rejects:
PlantID Quantity  Date
1       20        01/02/2012
1       3         02/02/2012
2       30        03/02/2012
.....

  Parts
PlantID Quantity  Date
1       300       01/02/2012
2       500       01/02/2012
1       600       02/02/2012
.......

I'm trying to join the three so that I have a sum of parts and rejects per plant between two dates:

Plant   Parts   Rejects
Plant1       900     23
Plant2       500     30
.....

I've tried joins, which just multiplies the sums and I've tried subqueries that won't let me use a date filter because it's not used in the group by clause.

Can anyone help?

Upvotes: 3

Views: 146

Answers (3)

Michael Fredrickson
Michael Fredrickson

Reputation: 37388

declare @startDate datetime, @endDate datetime
select @startDate = '1/1/2012', @endDate = '2/1/2012'

select
    p.Name as Plant,

    (select sum(Quantity) 
     from Parts 
     where PlantID = p.ID and date between @startDate and @endDate) as Parts,

    (select sum(Quantity) 
     from Rejects 
     where PlantID = p.ID and date between @startDate and @endDate) as Rejects
from
    Plant p
where
    p.endDate is null

Upvotes: 1

Mike
Mike

Reputation: 773

The following should work (using oracle):

SELECT NVL(p.plantid, r.plantid), NVL(parts,0), NVL(rejects,0)
FROM 
    (SELECT plantid, sum(quantity) as parts
    FROM parts
    WHERE date BETWEEN a AND b) p
        FULL JOIN
    (SELECT plantid, sum(quantity) as rejects
    FROM rejects
    WHERE date BETWEEN a and b) r
       ON p.plantid = r.plantid

Note that this will not get plants with no rejects or parts.

Upvotes: 0

aF.
aF.

Reputation: 66697

This will do the trick:

select p.Name as Plant,
       SUM(t.Quantity) as Parts,
       SUM(r.Quantity) as Rejects
from Plant p
inner join Rejects r on p.ID = r.PlantID
inner join Parts t on p.ID = PlantID
group by p.Name


You make the INNER JOIN between all tables using ID. Afterwards you just need the SUM of the Quantities in Parts and Rejects. For that you need to use the GROUP BY.

Upvotes: 0

Related Questions