×
INTELLIGENT WORK FORUMS
FOR COMPUTER PROFESSIONALS

Log In

Come Join Us!

Are you a
Computer / IT professional?
Join Tek-Tips Forums!
  • Talk With Other Members
  • Be Notified Of Responses
    To Your Posts
  • Keyword Search
  • One-Click Access To Your
    Favorite Forums
  • Automated Signatures
    On Your Posts
  • Best Of All, It's Free!

*Tek-Tips's functionality depends on members receiving e-mail. By joining you are opting in to receive e-mail.

Posting Guidelines

Promoting, selling, recruiting, coursework and thesis posting is forbidden.

Students Click Here

Summing two Columns from two separate tables

Summing two Columns from two separate tables

Summing two Columns from two separate tables

(OP)
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!

RE: Summing two Columns from two separate tables

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"

RE: Summing two Columns from two separate tables

Untested:

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

Ansi Core SQL-2003.

RE: Summing two Columns from two separate tables

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"

RE: Summing two Columns from two separate tables

(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:

... SELECT COALESCE(SUM(Quantity),0) ...

RE: Summing two Columns from two separate tables

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

-- Jason
"It's Just Ones and Zeros"

RE: Summing two Columns from two separate tables

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

Red Flag This Post

Please let us know here why this post is inappropriate. Reasons such as off-topic, duplicates, flames, illegal, vulgar, or students posting their homework.

Red Flag Submitted

Thank you for helping keep Tek-Tips Forums free from inappropriate posts.
The Tek-Tips staff will check this out and take appropriate action.

Reply To This Thread

Posting in the Tek-Tips forums is a member-only feature.

Click Here to join Tek-Tips and talk with other members! Already a Member? Login


Close Box

Join Tek-Tips® Today!

Join your peers on the Internet's largest technical computer professional community.
It's easy to join and it's free.

Here's Why Members Love Tek-Tips Forums:

Register now while it's still free!

Already a member? Close this window and log in.

Join Us             Close