Received: from localhost (maia-2.hub.org [200.46.204.187]) by postgresql.org (Postfix) with ESMTP id 0C2699FB69D for ; Tue, 27 Mar 2007 13:10:34 -0300 (ADT) Received: from postgresql.org ([200.46.204.71]) by localhost (mx1.hub.org [200.46.204.187]) (amavisd-new, port 10024) with ESMTP id 87105-09 for ; Tue, 27 Mar 2007 13:10:26 -0300 (ADT) X-Greylist: from auto-whitelisted by SQLgrey-1.7.4 Received: from sss.pgh.pa.us (sss.pgh.pa.us [66.207.139.130]) by postgresql.org (Postfix) with ESMTP id C7F259FB204 for ; Tue, 27 Mar 2007 13:10:26 -0300 (ADT) Received: from sss2.sss.pgh.pa.us (tgl@localhost [127.0.0.1]) by sss.pgh.pa.us (8.13.6/8.13.6) with ESMTP id l2RGAPFM027902; Tue, 27 Mar 2007 12:10:25 -0400 (EDT) To: "Claus Guttesen" cc: pgsql-sql@postgresql.org Subject: Re: select vs. select count In-reply-to: References: Comments: In-reply-to "Claus Guttesen" message dated "Tue, 27 Mar 2007 14:37:51 +0200" Date: Tue, 27 Mar 2007 12:10:24 -0400 Message-ID: <27901.1175011824@sss.pgh.pa.us> From: Tom Lane X-Virus-Scanned: Maia Mailguard 1.0.1 X-Archive-Number: 200703/321 X-Sequence-Number: 27959 "Claus Guttesen" writes: > select order_id from > (select o.order_id from orders o join order_lines ol using (order_id) > where o.time > '2006-01-01T00:00' and o.time < '2007-01-01T00:00' > and (ol.item_id = 10 or ol.item_id = 11 or ol.item_id = 12) group by > o.order_id) as prints > inner join > (select ho.order_id from orders ho join order_lines hol using (order_id) > where o.time > '2006-01-01T00:00' and o.time < '2007-01-01T00:00' > and (ol.item_id = 20 or ol.item_id = 21 or ol.item_id = 22) group by > o.order_id) as gifts > using (order_id) > except select order_id from > (select ho.order_id from orders ho join order_lines hol using (order_id) > where o.time > '2006-01-01T00:00' and o.time < '2007-01-01T00:00' > and (ol.item_id = 30 or ol.item_id = 31 or ol.item_id = 32) group > by o.order_id) as extra; > When I do the 'select order_id' I get (after scrolling down): > (1960 rows) > But when I do a 'select count(order_id) I get: > 2063 You did not show us exactly what you did, but if you simply blindly replaced "select order_id" with "select count(order_id)" in the first line above, then what you would have is select count(order_id) from some-stuff except select order_id from some-other-stuff so what you would get is either the count of some-stuff, or nothing at all if any of the order_ids in some-other-stuff chanced to equal the count. In any case it would not be the count of what the original EXCEPT query returned, unless the EXCEPT wasn't eliminating any rows. You need to wrap SELECT count(order_id) FROM ( ... ) around the entire EXCEPT query to get what you want. regards, tom lane