From: Claus Guttesen <kometen@gmail.com>
To: Tom Lane <tgl@sss.pgh.pa.us>
Cc: pgsql-sql@postgresql.org
Subject: Re: select vs. select count
Date: Fri, 30 Mar 2007 13:19:12 +0200
Message-ID: <b41c75520703300419i3e5251c9g7d4abd361bfc0bcc@mail.gmail.com> (raw)
In-Reply-To: <27901.1175011824@sss.pgh.pa.us>
References: <b41c75520703270537y12d83fe5w7fc58f6e35fbe13d@mail.gmail.com>
<27901.1175011824@sss.pgh.pa.us>
> > 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.
Thank you for your advise. It was the except-clause which gave me some
"headache". The query now looks like what you suggest:
select count(order_id)
(select order_id from
(select o.order_id from ... join orderlines ol using (order_id)
where ... as prints
inner join
(select o.order_id from ... join orderlines ol using (order_id)
where ... as extra
using (order_id)
except select order_id from
(select o.order_id from ... join orderlines ol using (order_id)
where ... as gifts)
as orders;
regards
Claus
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: kometen@gmail.com, tgl@sss.pgh.pa.us
Subject: Re: select vs. select count
In-Reply-To: <b41c75520703300419i3e5251c9g7d4abd361bfc0bcc@mail.gmail.com>
* 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