By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
429,335 Members | 2,718 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 429,335 IT Pros & Developers. It's quick & easy.

Comparing SUM of two tables

P: 5
Hi Everyone,
This one is driving me crazy.

I have three tables: purchase_order, purchase_order_bom, and rog_bom.

The table purchase_order_bom is a list of parts ordered for the purchase order. The table rog_bom is a list of parts received for the purchase order (ROG stands for receipt of goods).

I want to run a query that looks through table purchase_order but only show the purchase orders that have received less parts than the total number of parts order. Basically the purchase orders that haven't been fully received.

table purchase_order:
ID INT

table purchase_order_bom:
ID INT
po_ID INT
quantity INT
product_ID INT

table rog_bom:
ID INT
po_ID INT
quantity INT
product_ID INT

I thought it would be something like this (but this shows only one purchase_order row):

Expand|Select|Wrap|Line Numbers
  1. SELECT
  2.   *,
  3.   po.ID AS po_ID
  4. FROM
  5.   purchase_order po
  6. WHERE
  7.   (SELECT SUM(quantity) AS po_bom_total FROM purchase_order_bom po_bom WHERE po_bom.po_ID=po.ID) < (SELECT SUM(quantity) FROM rog_bom WHERE rog_bom.po_ID=po.ID)
  8.  
Thank you!
Andy
Dec 15 '11 #1

✓ answered by Rabbit

You need to flip your conditional. Right now you're only returning the ones that received more than they ordered.

Share this Question
Share on Google+
7 Replies


Rabbit
Expert Mod 10K+
P: 12,359
You need to flip your conditional. Right now you're only returning the ones that received more than they ordered.
Dec 15 '11 #2

P: 5
Hi Rabbit,

Good eye, I've changed it around so that ROG total is first. So it checks if ROG_bom < PO_bom. But it still only shows one PO, which actually has the ROG_bom total equeal to PO_bom, so it doesn't make sense.

There should be about 30 PO's that have less items received than ordered.

Am I writing the query correctly? Or am I totally off base?

Thanks!
Andy
Dec 19 '11 #3

Rabbit
Expert Mod 10K+
P: 12,359
What's does your current SQL look like?
Dec 19 '11 #4

P: 5
Expand|Select|Wrap|Line Numbers
  1. SELECT
  2.  *,
  3.  po.ID AS po_ID
  4. FROM
  5.  purchase_order po
  6. WHERE
  7.  (SELECT SUM(quantity) FROM rog_bom WHERE rog_bom.po_ID=po.ID) < (SELECT SUM(quantity) AS po_bom_total FROM purchase_order_bom po_bom WHERE po_bom.po_ID=po.ID)
  8.  
Is it clear what I'm trying to produce, or is that confusing as well?

Thank you,
Andy
Dec 19 '11 #5

P: 5
Expand|Select|Wrap|Line Numbers
  1. SELECT
  2.  *,
  3.  po.ID AS po_ID
  4. FROM
  5.  purchase_order po
  6. WHERE
  7.  (SELECT SUM(quantity) FROM rog_bom WHERE rog_bom.po_ID=po.ID) < (SELECT SUM(quantity) AS po_bom_total FROM purchase_order_bom po_bom WHERE po_bom.po_ID=po.ID)
  8.  
Is it clear what I'm trying to produce, or is that confusing as well?

Thank you,
Andy
Dec 19 '11 #6

Rabbit
Expert Mod 10K+
P: 12,359
That should work. Post some sample data of the records that should show up but aren't showing up.
Dec 19 '11 #7

P: 5
Rabbit,
As I was putting together some sample data for you I figured out that the query above does work but not when I applied it to my real data.

I found out that if there are no ROGs, it returns NULL instead of 0, so I had to use:

Expand|Select|Wrap|Line Numbers
  1. SELECT COALESCE(SUM(quantity),0) FROM rog_bom WHERE po_ID=po.ID
Thanks for your help, you got me to the solution
Dec 21 '11 #8

Post your reply

Sign in to post your reply or Sign up for a free account.