Skip site navigation (1) Skip section navigation (2)

Calculating the difference between result columns

From: "Keith Worthington" <keithw(at)narrowpathinc(dot)com>
To: "PostgreSQL Novice" <pgsql-novice(at)postgresql(dot)org>
Subject: Calculating the difference between result columns
Date: 2004-12-27 21:46:15
Message-ID: 20041227214615.M20847@narrowpathinc.com (view raw or flat)
Thread:
Lists: pgsql-novice
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


Responses

pgsql-novice by date

Next:From: Sukhdeep JoharDate: 2004-12-28 10:53:27
Subject: help with - psql: FATAL: Password authentication failed for user "gforge"
Previous:From: Bruno Wolff IIIDate: 2004-12-27 21:03:48
Subject: Re: Extracting data where a column is max

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group