Received: from maia.hub.org (unknown [200.46.208.211]) by mail.postgresql.org (Postfix) with ESMTP id 9604C6335AC for ; Wed, 24 Mar 2010 16:09:32 -0300 (ADT) Received: from mail.postgresql.org ([200.46.204.86]) by maia.hub.org (mx1.hub.org [200.46.208.211]) (amavisd-maia, port 10024) with ESMTP id 42353-06 for ; Wed, 24 Mar 2010 19:09:17 +0000 (UTC) X-Greylist: from auto-whitelisted by SQLgrey-1.7.6 Received: from smtp1.hi-media-techno.com (smtp1.hi-media-techno.com [195.158.241.85]) by mail.postgresql.org (Postfix) with ESMTP id AEFD9632919 for ; Wed, 24 Mar 2010 16:09:20 -0300 (ADT) Received: from localhost (localhost [127.0.0.1]) by smtp1.hi-media-techno.com (Postfix) with ESMTP id 2042D93BFE; Wed, 24 Mar 2010 20:09:19 +0100 (CET) Received: from smtp1.hi-media-techno.com ([127.0.0.1]) by localhost (smtp1.backbone.hi-media-techno.com [127.0.0.1]) (amavisd-new, port 10024) with ESMTP id q2dVfZxHbOdQ; Wed, 24 Mar 2010 20:09:18 +0100 (CET) Received: from mail1.hi-media-techno.com (unknown [192.168.27.17]) by smtp1.hi-media-techno.com (Postfix) with ESMTP id AE89E93BFC; Wed, 24 Mar 2010 20:09:18 +0100 (CET) Received: from localhost (localhost.localdomain [127.0.0.1]) by mail1.hi-media-techno.com (Postfix) with ESMTP id 4F67D28925; Wed, 24 Mar 2010 20:09:18 +0100 (CET) X-Virus-Scanned: amavisd-new at mail1.hi-media-techno.com Received: from mail1.hi-media-techno.com ([127.0.0.1]) by localhost (mail1.hi-media-techno.com [127.0.0.1]) (amavisd-new, port 10024) with ESMTP id aXMvLrgrEVgX; Wed, 24 Mar 2010 20:09:18 +0100 (CET) Received: from localhost (89-158-241-71.rev.dartybox.com [89.158.241.71]) by mail1.hi-media-techno.com (Postfix) with ESMTPSA id 2F485CF07C; Wed, 24 Mar 2010 20:09:18 +0100 (CET) From: Dimitri Fontaine To: Guy Deleeuw Cc: pgsql-admin@postgresql.org Subject: Re: How to use an alias name in the current select Organization: Hi-Media References: <4BAA40E2.3000805@eurofer.be> User-Mail-Address: dfontaine@hi-media.com Date: Wed, 24 Mar 2010 20:09:18 +0100 In-Reply-To: <4BAA40E2.3000805@eurofer.be> (Guy Deleeuw's message of "Wed, 24 Mar 2010 17:42:10 +0100") Message-ID: User-Agent: Gnus/5.13 (Gnus v5.13) Emacs/23.1.50 (darwin) MIME-Version: 1.0 Content-Type: text/plain; charset=us-ascii X-Virus-Scanned: Maia Mailguard 1.0.1 X-Spam-Status: No, hits=-2.341 tagged_above=-10 required=5 tests=AWL=0.258, BAYES_00=-2.599 X-Spam-Level: X-Archive-Number: 201003/184 X-Sequence-Number: 33983 Hi, Guy Deleeuw writes: > SELECT > t_orders_articles.k_id AS k_id > ,t_orders_articles.quantity AS order_qty > ,(SELECT sum(quantity) FROM t_deliveries_articles WHERE article_id = > t_orders_articles.k_id) AS delivery_qty > , (t_orders_articles.quantity - delivery_qty) AS qty > FROM t_orders_articles > ......; > > How can I make that without having 2 select call ? Put it as a relation in a subquery, or use WITH if using 8.4. SELECT k_id, order_qty, quantity, delivery_qty, quantity - delivery_qty as qty FROM ( SELECT t_orders_articles.k_id AS k_id ,t_orders_articles.quantity AS order_qty ,(SELECT sum(quantity) FROM t_deliveries_articles WHERE article_id = t_orders_articles.k_id) AS delivery_qty , t_orders_articles.quantity FROM t_orders_articles ) as t; Or WITH t AS ( SELECT t_orders_articles.k_id AS k_id ,t_orders_articles.quantity AS order_qty ,(SELECT sum(quantity) FROM t_deliveries_articles WHERE article_id = t_orders_articles.k_id) AS delivery_qty , t_orders_articles.quantity FROM t_orders_articles ) SELECT k_id, order_qty, quantity, delivery_qty, quantity - delivery_qty as qty FROM t; Regards, -- dim