From: Claus Guttesen <kometen@gmail.com>
To: pgsql-sql@postgresql.org
Subject: Re: select vs. select count
Date: Tue, 27 Mar 2007 14:39:08 +0200
Message-ID: <b41c75520703270539l333285edwf5187ca35e27b13c@mail.gmail.com> (raw)
In-Reply-To: <b41c75520703270537y12d83fe5w7fc58f6e35fbe13d@mail.gmail.com>
References: <b41c75520703270537y12d83fe5w7fc58f6e35fbe13d@mail.gmail.com>
Forgot to mention that this is on postgresql 7.4.14 and FreeBSD 6.2.
regards
Claus
> Hi.
>
> I'm performing the following query to get all items sold in 2006 which
> are in category prints or gifts, but not in extra:
>
> 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):
>
> order_id
> ---------
> xyz
> ...
> foo
> bas
> (1960 rows)
>
> But when I do a 'select count(order_id) I get:
>
> count
> -------
> 2063
>
> Why does select and select(count) produce two different results? Am I
> doing something wrong here?
>
> 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
Subject: Re: select vs. select count
In-Reply-To: <b41c75520703270539l333285edwf5187ca35e27b13c@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