DomainKey-Signature: a=rsa-sha1; c=nofws; d=gmail.com; s=beta;
	h=received:message-id:date:from:to:subject:cc:in-reply-to:mime-version:content-type:content-transfer-encoding:content-disposition:references;
	b=djfdrHWPcZ+5hCnta6xV9O7O5/+QYNQ2M34IAPcMtl3sho+08eDXuFQf8AIUOSl20B0DqFj3kAz8hg/jQLvWepnCvQ8QWZW8cNF3PWS7jpsuHDBgc/JtL3i2f9u3uKMflHVfPuG6O+hrZ8xnuU6eS+bmCs6U2hBAQHUEnLMqFGI=
Message-ID: <b41c75520703300419i3e5251c9g7d4abd361bfc0bcc@mail.gmail.com>
Date: Fri, 30 Mar 2007 13:19:12 +0200
From: "Claus Guttesen" <kometen@gmail.com>
To: "Tom Lane" <tgl@sss.pgh.pa.us>
Subject: Re: select vs. select count
Cc: pgsql-sql@postgresql.org
In-Reply-To: <27901.1175011824@sss.pgh.pa.us>
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>
	<27901.1175011824@sss.pgh.pa.us>

> > 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):
> > (1960 rows)
>
> > But when I do a 'select count(order_id) I get:
> >   2063
>
> You did not show us exactly what you did, but if you simply blindly
> replaced "select order_id" with "select count(order_id)" in the first
> line above, then what you would have is
>         select count(order_id) from some-stuff
>         except
>         select order_id from some-other-stuff
> so what you would get is either the count of some-stuff, or nothing
> at all if any of the order_ids in some-other-stuff chanced to equal
> the count.  In any case it would not be the count of what the original
> EXCEPT query returned, unless the EXCEPT wasn't eliminating any rows.
>
> You need to wrap SELECT count(order_id) FROM ( ... ) around the entire
> EXCEPT query to get what you want.

Thank you for your advise. It was the except-clause which gave me some
"headache". The query now looks like what you suggest:

select count(order_id)
 (select order_id from
  (select o.order_id from ... join orderlines ol using (order_id)
where ... as prints
   inner join
  (select o.order_id from ... join orderlines ol using (order_id)
where ... as extra
   using (order_id)
  except select order_id from
  (select o.order_id from ... join orderlines ol using (order_id)
where ... as gifts)
 as orders;

regards
Claus