From: Claus Guttesen <kometen@gmail.com>
To: pgsql-sql@postgresql.org
Subject: select vs. select count
Date: Tue, 27 Mar 2007 14:37:51 +0200
Message-ID: <b41c75520703270537y12d83fe5w7fc58f6e35fbe13d@mail.gmail.com> (raw)
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: <b41c75520703270537y12d83fe5w7fc58f6e35fbe13d@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