Thread: Calculating the difference between result columns

Hi All,

I'm stuck again.  I have written two queries.  The first calculates the total
quantity of items that have been ordered.  The second calculates the total
quantity of items that have been received.  Now I want to find the difference
which will tell me what is still on order.  Any hints would be appreciated.

SELECT purchase_order.tbl_line_item.item_id,
       sum( purchase_order.tbl_line_item.quantity ) AS ordered
  FROM purchase_order.tbl_line_item
  JOIN purchase_order.tbl_detail
 USING (po_number)
 WHERE NOT purchase_order.tbl_detail.closed
 GROUP BY purchase_order.tbl_line_item.item_id;

SELECT purchase_order.tbl_line_item.item_id,
       sum(purchase_order.tbl_receiving.quantity) AS received
  FROM purchase_order.tbl_line_item
  JOIN purchase_order.tbl_receiving
    ON ( purchase_order.tbl_line_item.po_number =
purchase_order.tbl_receiving.po_number AND
         purchase_order.tbl_line_item.po_line =
purchase_order.tbl_receiving.po_line )
  JOIN purchase_order.tbl_detail
    ON ( purchase_order.tbl_line_item.po_number =
purchase_order.tbl_detail.po_number)
 WHERE NOT purchase_order.tbl_detail.closed
 GROUP BY purchase_order.tbl_line_item.item_id;

Kind Regards,
Keith

______________________________________________
99main Internet Services http://www.99main.com

"Keith Worthington" <keithw@narrowpathinc.com> writes:
> I'm stuck again.  I have written two queries.  The first calculates the total
> quantity of items that have been ordered.  The second calculates the total
> quantity of items that have been received.  Now I want to find the difference
> which will tell me what is still on order.

Something like

    SELECT o.item_id, ordered - received FROM
      (SELECT ...) o
      (SELECT ...) r
    WHERE o.item_id = r.item_id;

I'm not sure how efficient this mess is going to be :-(.  If you intend
to do this a lot, you might want to think about a different database
structure that doesn't require quite so much joining to find out
anything...

            regards, tom lane

unsubscribe
end