mysql – Make a query in two tables and present the amount of the group that has the most records of the foreign key

Question:

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

Class Table:

class_id name Class class_code
1 1A 20201Aespertino
two 2A 20202Aespertino
3 3A 20203Aespertino
4 4A 20204Aespertino
5 5A 20205Apertino
6 6A 20206Aespertino
7 7A 20207Aespertino
8 8A 20208Apertino
9 9A 20209Aespertino

Student Table:

student_id student_name student_surname class_id
1 Caesar august 1
two mario Nunes 1
3 Andrew Silva 1
4 Ricardo Novaes 1
5 Ruth Maria two
6 Maria Rita two
7 Elisa honey two
8 osmarildo Souza two
9 Raimundo Silva 3

The SELECT I need should show this result:

class_id name Class class_code student_quantity
1 1A 20201Aespertino 4
two 2A 20202Aespertino 4

I got a SELECT that shows all the class ids with their respective amounts 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:

class_id number of students
1 4
two 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 number_students
1st in the afternoon 20201Aespertino 4
2 The afternoon 20202Aespertino 4
3 The afternoon 20203Aespertino 1

As it is, the second SELECT is showing the number of all classes, but I need a SELECT that would show only the first two classes since both have the same number of students and are the classes that have more students. And if, for example, there was another class with 5 students, the SELECT should return only this other class.

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

class_id name Class class_code student_quantity
1 1 to 20201Aespertino 4
two 2 A 20202Aespertino 4

But I still need the SELECT have the id_turma , but as I'm putting a WHERE to link the two tables (student with class through 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 suggested by 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;

Result:

name Class class_code student_quantity
1st in the afternoon 20201Aespertino 4

But it is still without the id_turma because if you put the id_turma generates the error mentioned above. And with this LIMIT 1 doesn't show other(s) classes with the same amount, it only shows the first record excluding the other(s) that have or have the same amount.

Answer:

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)
Scroll to Top