Re: select vs. select count

"Claus Guttesen" <kometen(at)gmail(dot)com> writes:
> 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.

regards, tom lane