I have two tables:

TABLE students
(
    STUDENT_ID smallint GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
    GROUP_ID smallint,
);

TABLE groups
(
    GROUP_ID smallint GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
    GROUP_NAME char(5)
);

Each group has a certain number of students.

To find out, I use this query:

SELECT 
    groups.group_name, COUNT (*)
FROM
    students
JOIN 
    groups ON students.group_id = groups.group_id 
GROUP BY 
    groups.group_name;

Output:

Now the question:

1. How do I get groups with the same number of students?

2. How do I get the groups with the least number of students?

CodePudding user response：

In order to get groups with the same number of students, you can assign a ranking, grouping on your counts_, then picking those which have at least a rank 2 (which means that at least one count_ is repeated twice).

WITH tab AS (
    SELECT groups.group_name, 
           COUNT(*) AS count_
    FROM       students
    INNER JOIN groups 
            ON students.group_id = groups.group_id 
    GROUP BY groups.group_name
), cte AS (
    SELECT group_name,
           count_,
           ROW_NUMBER() OVER(
               PARTITION BY count_ 
               ORDER     BY group_name
           ) AS rn
    FROM tab
)
SELECT * 
FROM tab 
WHERE count_ IN (SELECT count_ FROM cte WHERE rn = 2);

Instead if you want to get the least number of students, it suffices getting all counts which value count is minimum, using a subquery:

WITH tab AS (
    SELECT groups.group_name, 
           COUNT(*) AS count_
    FROM       students
    INNER JOIN groups 
            ON students.group_id = groups.group_id 
    GROUP BY groups.group_name
)
SELECT * 
FROM tab 
WHERE count_ IN (SELECT MIN(count_) FROM tab);

Check the demo here.

Note that your initial query is employed within a common table expression inside these solutions.