Re: a select within a count ??

From: Chuck <chuckh_at_softhome.net>
Date: 1 Jul 2003 13:33:27 GMT
Message-ID: <Xns93AB61359D82Bchuckhsofthomenet@130.133.1.4>

Chuck <chuckh_at_softhome.net> wrote in
news:Xns93AB60377BC40chuckhsofthomenet_at_130.133.1.4:

> avanrossem_at_hotmail.com (Andre) wrote in news:4d32d1be.0307010018.44650517

> @posting.google.com:
> 

>> Hello all,
>>
>> I have a query, but it's not working.
>>
>> Can someone help me:
>>
>> select count(*), count(select distinct My_id from Anytable where My_id
>> < 1000) from Anytable;
>>
>> In other words I want to execute a subquery within a count-statement.
>> Is it possible ???
>>
>> Thx!,
>> Andre

> 
> Looks like you are trying to get a total record count, and a count of 
> only those where my_id < 1000. Try this...
> 
> select
>   count(*)
> , sum(case when my_id < 1000 then 1 else 0 end)
> from
>   anytable;
> 
> 

Ooops, I missed the distinct. Change that select to this...

select
  a.cnt, b.cnt
from
  (select count(*) cnt from anytable) a,   (select count(distinct my_id) cnt from anytable where my_id < 1000) b Received on Tue Jul 01 2003 - 08:33:27 CDT