Query two tables and display the quantity of the group that has the most foreign key records

Question

I need to make a query between the following two tables:

Table Class:

id_turma	classmate	class code
1	1A	20201AVesperto
2	2A	20202AVespertino
3	3A	20203AVespertino
4	4A	20204AVsmarter
5	5A	20205AVesperto
6	6A	20206AVespertino
7	7A	20207AVespertino
8	8A	20208AVsmarter
9	9A	20209AVesperto

Table Student:

id_student	student name	last name	id_turma
1	Cesar	Augusto	1
2	Mario	Nunes	1
3	André	Silva	1
4	Ricardo	Novaes	1
5	Ruth	Maria	2
6	Maria	Rita	2
7	Elisa	Melo	2
8	Osmarildo	Souza	2
9	Raimundo	Silva	3

The SELECT that I am in need should show this result:

id_turma	classmate	class code	student quantity
1	1A	20201AVesperto	4
2	2A	20202AVespertino	4

I got a SELECT which shows all class ids with their respective quantities but I’m not able to filter only what has more records.

select 
  id_turma, count(id_aluno) AS quantidade_aluno 
from aluno 
group by id_turma;

Returning this result:

id_turma	student amount
1	4
2	4
3	1

But I need the result involving the two tables and showing a result with the largest class or larger classes if they have equal amounts:

SELECT 
  nome_turma, código_turma, COUNT(id_aluno) AS quantidade_aluno 
FROM 
  aluno, turma 
WHERE 
  aluno.id_turma = turma.id_turma 
GROUP BY nome_turma;

With the SELECT above I have the following result:

name_group	code_group	student turnover
1 The Evening	20201AVesperto	4
2 The Evening	20202AVespertino	4
3 The Evening	20203AVespertino	1

The way the second one is SELECT is presenting the amount of all classes, but I need a SELECT that would show only the first two classes since both have the same amount of students and are the classes that have the most students. And if, for example, there was another class still with 5 students SELECT should return only this other class.

As in the example above the expected result would be this:

id_turma	classmate	class code	student quantity
1	1 A	20201AVesperto	4
2	2 A	20202AVespertino	4

But I still need SELECT has the id_turma, but how am I putting a WHERE to link the two tables (student with class through the id_turma) it rejects generating an error as follows:

SELECT 
  id_turma, nome_turma, codigo_turma, COUNT(id_aluno) AS quantidade_aluno 
FROM 
  aluno, turma 
WHERE 
  aluno.id_turma = turma.id_turma 
GROUP BY nome_turma;

ERROR 1052 (23000): Column 'id_turma' in field list is ambiguous

As the suggestion of the colleague who commented on the POST, I put a TOP 1 as follows:

SELECT 
  nome_turma, codigo_turma, COUNT(id_aluno) AS quantidade_aluno 
FROM 
  aluno, turma 
WHERE 
  aluno.id_turma = turma.id_turma 
GROUP BY nome_turma 
ORDER BY quantidade_aluno DESC 
LIMIT 1;

Upshot:

classmate	class code	student quantity
1 The Evening	20201AVesperto	4

But you’re still without the id_turma because if you put the id_turma generates the error mentioned above. And with this LIMIT 1 does not display another class(s) with the same amount, only shows the first record excluding the other(s) that has or has the same amount.

Answer 1

I believe this query meets your needs:

WITH 
    Qtd_Turmas AS (
        SELECT 
          id_turma, COUNT(id_aluno) AS qtd 
        FROM 
          Aluno 
        GROUP BY id_turma
    ),
    Max_Turmas AS (
        SELECT 
          id_turma, MAX(qtd) AS max_qtd 
        FROM
          Qtd_Turmas)
    )
    
SELECT 
  T.id_turma, T.nome_turma, T.codigo_turma, MT.max_qtd
FROM 
  Turma T 
INNER JOIN Max_Turmas MT 
  ON (T.id_turma = MT.id_turma)