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