select vs. select count

From: "Claus Guttesen" <kometen(at)gmail(dot)com>
To: pgsql-sql(at)postgresql(dot)org
Subject: select vs. select count
Date: 2007-03-27 12:37:51
Message-ID: b41c75520703270537y12d83fe5w7fc58f6e35fbe13d@mail.gmail.com
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-sql

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

Responses

Browse pgsql-sql by date

  From Date Subject
Next Message Claus Guttesen 2007-03-27 12:39:08 Re: select vs. select count
Previous Message Sabin Coanda 2007-03-27 06:47:22 array_to_string