Received: from localhost (maia-5.hub.org [200.46.204.182])
	by postgresql.org (Postfix) with ESMTP id BC3CE9FB5C2
	for <pgsql-sql-postgresql.org@postgresql.org>;
	Tue, 27 Mar 2007 09:39:15 -0300 (ADT)
Received: from postgresql.org ([200.46.204.71])
	by localhost (mx1.hub.org [200.46.204.182]) (amavisd-new, port 10024)
	with ESMTP id 53859-02 for <pgsql-sql-postgresql.org@postgresql.org>;
	Tue, 27 Mar 2007 09:39:05 -0300 (ADT)
X-Greylist: domain auto-whitelisted by SQLgrey-1.7.4
Received: from wr-out-0506.google.com (wr-out-0506.google.com
 [64.233.184.239])
	by postgresql.org (Postfix) with ESMTP id 77C0B9FB5BD
	for <pgsql-sql@postgresql.org>; Tue, 27 Mar 2007 09:39:10 -0300 (ADT)
Received: by wr-out-0506.google.com with SMTP id 70so1145097wra
	for <pgsql-sql@postgresql.org>; Tue, 27 Mar 2007 05:39:09 -0700 (PDT)
DKIM-Signature: a=rsa-sha1; c=relaxed/relaxed; d=gmail.com; s=beta;
	h=domainkey-signature:received:received:message-id:date:from:to:subject:in-reply-to:mime-version:content-type:content-transfer-encoding:content-disposition:references;
	b=bt3GaIc7rreEAkfvUnlju8H21scK/l1NgJzOqaWYQwGwCfbmr0UmDH970zsCv9F2ekxWUzxotxjG+9Nb/JaH2ow+8n6obvSK39smjw80mM29YW2y4l64yLSSdDXXFDmY4cV0qa3+BxJNeFElcYCvdddajefm7u2MDIv619wXu8Q=
DomainKey-Signature: a=rsa-sha1; c=nofws; d=gmail.com; s=beta;
	h=received:message-id:date:from:to:subject:in-reply-to:mime-version:content-type:content-transfer-encoding:content-disposition:references;
	b=Uh4CBl4HR7LRwR42FW2Yp3kghKSCAhLURO3poYyGbRkWkHksW/zAFnDOiAbtxmZ+YDnxv8OZNZcQiiNtlf5DyelM1FP8EdAqAVxZOf4w4l2QTcLdN/fmq2CiZ1a4PRvLM+1CTL35xhUaFjFIngehvdniyVBoAcQqze5ZQ041zow=
Received: by 10.114.75.1 with SMTP id x1mr3148710waa.1174999148588;
	Tue, 27 Mar 2007 05:39:08 -0700 (PDT)
Received: by 10.114.194.12 with HTTP; Tue, 27 Mar 2007 05:39:08 -0700 (PDT)
Message-ID: <b41c75520703270539l333285edwf5187ca35e27b13c@mail.gmail.com>
Date: Tue, 27 Mar 2007 14:39:08 +0200
From: "Claus Guttesen" <kometen@gmail.com>
To: pgsql-sql@postgresql.org
Subject: Re: select vs. select count
In-Reply-To: <b41c75520703270537y12d83fe5w7fc58f6e35fbe13d@mail.gmail.com>
MIME-Version: 1.0
Content-Type: text/plain; charset=ISO-8859-1; format=flowed
Content-Transfer-Encoding: 7bit
Content-Disposition: inline
References: <b41c75520703270537y12d83fe5w7fc58f6e35fbe13d@mail.gmail.com>
X-Virus-Scanned: Maia Mailguard 1.0.1
X-Archive-Number: 200703/311
X-Sequence-Number: 27949

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
>