DomainKey-Signature: a=rsa-sha1; c=nofws; d=gmail.com; s=beta;
	h=received:message-id:date:from:to:subject:mime-version:content-type:content-transfer-encoding:content-disposition;
	b=AZTRSL/17NllTy3X7D6LJx5OfEIqFiUnOOD3WZrZmQ4zadiEUrewXOSyXwAYyEHaRd5QnWs6WdmiZVRX8EKqVlKY6MWM7LgFbCrVh/G0/2wl+KW0pWjHZ1oeNw4UD028ihmamR0r4eRvI0LMprSuRSQgNgEmGaof6gtMI+wgBFE=
Message-ID: <b41c75520703270537y12d83fe5w7fc58f6e35fbe13d@mail.gmail.com>
Date: Tue, 27 Mar 2007 14:37:51 +0200
From: "Claus Guttesen" <kometen@gmail.com>
To: pgsql-sql@postgresql.org
Subject: select vs. select count
MIME-Version: 1.0
Content-Type: text/plain; charset=ISO-8859-1; format=flowed
Content-Transfer-Encoding: 7bit
Content-Disposition: inline

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