Summing two Columns from two separate tables

[dbss27]

Thanks for reading my post. I'm trying to find the SUM of two colums from two different tables. The tables I'm using are as follows:

Table: Parts
id Name Type
--------------------
1 Screw Quarter
2 Screw Half
3 Nail Half
4 Nail Quarter

Table: Purchases
id Quantity
----------------
1 10
2 10
1 5
3 20
4 7

Table: Sold
id Quantity
-----------------
1 5
2 3
1 5
3 10
1 4

I'm trying to write a SQL statement that will produce the following result:

Name Type Purchase Sold
-------------------------------------------
Screw Quarter 15 14
Screw half 10 3
Nail half 20 10
Nail Quarter 7 0


My SQL statement is not producing the desired table, it is as follows:

SELECT Parts.Name, Parts.Type, SUM(Purchase.Quantity), SUM(Sold.Quantity)
FROM (Parts INNER JOIN Purchases ON Parts.ID = Purchases.ID) INNER JOIN Sold ON Parts.ID = Sold.ID)
GROUP BY Parts.Name, Parts.Type;

Any suggestions would be greatly apprecaited.

Thanks!

 

[jdemmi]

try this -- i changed the tables and fields to test my query in my db..i then changed them back -- so i hope i didn't miss something

select
purchases.id, case when sum(purchases.qty) is null then 0 else sum(purchases.qty) end qty_purchased,
case when sum(sold.qty) is null then 0 else sum(sold.qty) end qty_sold,
name, type from purchases
FULL OUTER JOIN sold ON purchases.ID = sold.ID
FULL OUTER JOIN PRT on purchases.ID = parts.ID
group by purchases.id, sold.id, name, type

-- Jason
"It's Just Ones and Zeros

 

[JarlH]

Untested:
[tt]
SELECT parts.name,
parts.type,
(SELECT SUM(Quantity) FROM Purchases
WHERE Purchases.id = parts.id) AS purchase,
(SELECT SUM(Quantity) FROM Sold
WHERE Sold.id = parts.id) AS sold
FROM parts
[/tt]
Ansi Core SQL-2003.

 

[jdemmi]

That should work and is a lot cleaner.

(The case statement could be added to accomodate 0 values)

-- Jason
"It's Just Ones and Zeros

 

[JarlH]

(The case statement could be added to accomodate 0 values)

Thats true!

Even better to use COALESCE and save some typing and make things easier to read:

[tt]... SELECT COALESCE(SUM(Quantity),0) ...[/tt]

 

[jdemmi]

I ALWAYS forget about COALESCE....hard to change habits

-- Jason
"It's Just Ones and Zeros

 

[dbss27]

Thanks for the quick reply guys. Your suggestions worked great! Thanks a lot for your help.