SQL: Finding the Minimum aggregate using only count

Question

I'm using DB2 for a project and looking to find which Group has the fewest members without using the min feature. My idea is to find all the groups and then subtract out any group which has more members from some other group thus leaving me with the group with that has no more members than any other group, i.e. the min.

So far I have

SELECT DISTINCT P.group as Group, count(P.id) as Count
FROM People P
EXCEPT
SELECT P.group, count(P.id) 
FROM People P, People O
WHERE count(P.cid) > count(O.cid);

With a schema for People like

create table People (
    group  varchar(25)  not null,
    id   smallint     not null,
);

I am getting the following error:

SQL0119N An expression starting with "CLUB" specified in a SELECT clause, HAVING clause, or ORDER BY clause is not specified in the GROUP BY clause or it is in a SELECT clause, HAVING clause, or ORDER BY clause with a column function and no GROUP BY clause is specified. SQLSTATE=42803

If you could help point out what I am doing wrong or the correct format for such a query it would be greatly appreciated!

GMB GMB · Accepted Answer · 2020-03-16T23:21:13

find which Group has the fewest members

You can aggregate by group, order by member count, and fetch the top row only:

select p.group as grp, count(*) as cnt
from people p
group by p.group
order by count(*)
fetch first 1 rows only

SQL: Finding the Minimum aggregate using only count

2 Answers