Thread: count() for a select statement?

Is there an efficient way to get a count of the number of rows returned
by this kind of query?

select id from products where name ILIKE 'TNT' UNION select id from
products where name ILIKE 'ATOM' UNION select id from products where
name ILIKE 'BOB'

I don't need any of the data returned, just a row count. The way I do it
now is to execute the query and use (in PHP) pg_numrows() on the result
set. But that mean the whole result set is being sent back to me when
all I need is a count ...

I've tried select count( select id from ...) but that didn't work ;)

Jc

On Wed, 2 Oct 2002, Jean-Christian Imbeault wrote:

> Is there an efficient way to get a count of the number of rows returned
> by this kind of query?
>
> select id from products where name ILIKE 'TNT' UNION select id from
> products where name ILIKE 'ATOM' UNION select id from products where
> name ILIKE 'BOB'

Probably "select count(*) from (select id from ... ) as t"
should work.

On Tuesday 01 October 2002 07:46 pm, Jean-Christian Imbeault wrote:
> Is there an efficient way to get a count of the number of rows returned
> by this kind of query?
>
> select id from products where name ILIKE 'TNT' UNION select id from
> products where name ILIKE 'ATOM' UNION select id from products where
> name ILIKE 'BOB'
>
> I don't need any of the data returned, just a row count. The way I do it
> now is to execute the query and use (in PHP) pg_numrows() on the result
> set. But that mean the whole result set is being sent back to me when
> all I need is a count ...
>
> I've tried select count( select id from ...) but that didn't work ;)
>

You try :
select count(*)
from (select id from ...)

regards
haris peco