User Profile

tracerstevens · Dec 21 '11, 07:39 PM

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:

Code:

SELECT COALESCE(SUM(quantity),0) FROM rog_bom WHERE po_ID=po.ID

Thanks for your help, you got me to the solution

tracerstevens · Dec 19 '11, 10:10 PM

Code:

SELECT
 *,
 po.ID AS po_ID
FROM
 purchase_order po
WHERE
 (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)

Is it clear what I'm trying to produce, or is that confusing as well?

Thank you,
Andy

tracerstevens · Dec 19 '11, 10:10 PM

Code:

SELECT
 *,
 po.ID AS po_ID
FROM
 purchase_order po
WHERE
 (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)

Is it clear what I'm trying to produce, or is that confusing as well?

Thank you,
Andy

tracerstevens · Dec 19 '11, 09:39 PM

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

User Profile

Profile Sidebar

Leave a comment:

Leave a comment:

Leave a comment:

Leave a comment:

Comparing SUM of two tables