Re: select vs. select count

> > 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