From: Tom Lane <tgl@sss.pgh.pa.us>
To: Claus Guttesen <kometen@gmail.com>
Cc: pgsql-sql@postgresql.org
Subject: Re: select vs. select count
Date: Tue, 27 Mar 2007 12:10:24 -0400
Message-ID: <27901.1175011824@sss.pgh.pa.us> (raw)
In-Reply-To: <b41c75520703270537y12d83fe5w7fc58f6e35fbe13d@mail.gmail.com>
References: <b41c75520703270537y12d83fe5w7fc58f6e35fbe13d@mail.gmail.com>
"Claus Guttesen" <kometen@gmail.com> 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
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-sql@postgresql.org
Cc: tgl@sss.pgh.pa.us, kometen@gmail.com
Subject: Re: select vs. select count
In-Reply-To: <27901.1175011824@sss.pgh.pa.us>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox