Re: Many aggregates in one query

Matti Teppo <matti.teppo_at_nospam.deio.net> wrote in message news:<3B7BCA1D.B40FBD52_at_nospam.deio.net>...
> I have a problem, for which I'm sure there is a simple answer but I
> can't see it.
>
> I've a table called "test" with following rows:
> id
> -----------
> 1000
> 1000
> 1000
> 1000
> 1001
> 1001
> 1002
> 1002
> 1003
> 1003
> 1003
>
> I want to get the smallest wsid having the smallest amount of rows.
> Result set I want is:
> wsid rowcnt
> ----------- -----------
> 1001 2
>
> The following returns all the rows that have the smallest amount of
> rows, but how to get only the smallest wsid?
> select wsid, count(wsid) as rowcnt
> from test
> group by wsid
> having count(wsid) = min(count(wsid))
>
> wsid rowcnt
> ----------- -----------
> 1001 2
> 1002 2
>
> Adding wsid = min(wsid) to having clause does not help.
>
> Thank you in advance

Eh... this is really cheating, but I am going to use it anyway -

create view testview as select wsid, count(wsid) as fa from test group by wsid;
select wsid, fa from testview where fa<(select fa from testview);\p\g

Theoretically you should be able to join the two statements and eliminate the need for views (can't think right now - mental block).

I would be glad to know if anyone is able to join them. I am using II 2.0/0001 (hp8.us5/00) on HP 10.20.

• Saurabh.