pg.ddx.io pgsql-sql@postgresql.org mailing list archive
help / color / mirror / Atom feedselect vs. select count
5+ messages / 3 participants
[nested] [flat]
* select vs. select count
@ 2007-03-27 12:37 Claus Guttesen <kometen@gmail.com>
0 siblings, 3 replies; 5+ messages in thread
From: Claus Guttesen @ 2007-03-27 12:37 UTC (permalink / raw)
To: pgsql-sql
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
^ permalink raw reply [nested|flat] 5+ messages in thread
* Re: select vs. select count
@ 2007-03-27 12:39 Claus Guttesen <kometen@gmail.com>
parent: Claus Guttesen <kometen@gmail.com>
2 siblings, 0 replies; 5+ messages in thread
From: Claus Guttesen @ 2007-03-27 12:39 UTC (permalink / raw)
To: pgsql-sql
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
>
^ permalink raw reply [nested|flat] 5+ messages in thread
* Re: select vs. select count
@ 2007-03-27 14:20 Peter Eisentraut <peter_e@gmx.net>
parent: Claus Guttesen <kometen@gmail.com>
2 siblings, 0 replies; 5+ messages in thread
From: Peter Eisentraut @ 2007-03-27 14:20 UTC (permalink / raw)
To: pgsql-sql; +Cc: Claus Guttesen <kometen@gmail.com>
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/
^ permalink raw reply [nested|flat] 5+ messages in thread
* Re: select vs. select count
@ 2007-03-27 16:10 Tom Lane <tgl@sss.pgh.pa.us>
parent: Claus Guttesen <kometen@gmail.com>
2 siblings, 1 reply; 5+ messages in thread
From: Tom Lane @ 2007-03-27 16:10 UTC (permalink / raw)
To: Claus Guttesen <kometen@gmail.com>; +Cc: pgsql-sql
"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 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
^ permalink raw reply [nested|flat] 5+ messages in thread
* Re: select vs. select count
@ 2007-03-30 11:19 Claus Guttesen <kometen@gmail.com>
parent: Tom Lane <tgl@sss.pgh.pa.us>
0 siblings, 0 replies; 5+ messages in thread
From: Claus Guttesen @ 2007-03-30 11:19 UTC (permalink / raw)
To: Tom Lane <tgl@sss.pgh.pa.us>; +Cc: pgsql-sql
> > 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
^ permalink raw reply [nested|flat] 5+ messages in thread
end of thread, other threads:[~2007-03-30 11:19 UTC | newest]
Thread overview: 5+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2007-03-27 12:37 select vs. select count Claus Guttesen <kometen@gmail.com>
2007-03-27 12:39 ` Claus Guttesen <kometen@gmail.com>
2007-03-27 14:20 ` Peter Eisentraut <peter_e@gmx.net>
2007-03-27 16:10 ` Tom Lane <tgl@sss.pgh.pa.us>
2007-03-30 11:19 ` Claus Guttesen <kometen@gmail.com>
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox