Обсуждение: select vs. select count
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 holusing (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
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 >
Claus Guttesen wrote: > Why does select and select(count) produce two different results? count(expression) only counts nonnull values. -- Peter Eisentraut http://developer.postgresql.org/~petere/
"Claus Guttesen" <kometen@gmail.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 isselect count(order_id) from some-stuffexceptselect 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
> > 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