Postgres 15

By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.

create table tbl_school_years (
  id int,
  id_student int,
  id_class int,
  year int
);

insert into tbl_school_years values
(1, 1, 1, 2022),
(2, 2, 1, 2022),
(3, 4, 2, 2022);

CREATE TABLE



INSERT 0 3


SELECT id_class, COUNT(id_student) as total_student
FROM tbl_school_years 
GROUP BY id_class


id_class
total_student


2
1

1
2



SELECT 2


with cte as (
  select t.id, s.*
  from tbl_school_years t
  inner join (
    SELECT id_class, COUNT(id_student) as total_student
    FROM tbl_school_years 
    GROUP BY id_class
  ) as s on s.id_class = t.id_class
)
select min(id), id_class, total_student
from cte
group by id_class, total_student


min
id_class
total_student


1
1
2

3
2
1



SELECT 2