How to get the sum of three tables in sql

Asked

Viewed 452 times

3

I have 3 tables, being them:

  • Schoolhouse: id, id_escola and nome_escola;
  • Professor: id, id_escola and nome_professor;
  • Students: id, id_escola, sala_aula and numero_alunos;

I am aware of the joins, count's, and sum'but I’m not getting to ride the SQL.

I need to return a line from a specific school with the name of the school, number of students, number of classrooms and number of teachers.

How to mount this query ?

mysql sql mysqli

2 answers

1

Try that consult you’re doing. But so... you can improve the structure of your tables.

The table school does not need two primary keys unless required.
On the table students does not need the column numero_alunos.

SELECT 
    NOME_ESCOLA,
    NOME_PROFESSOR,
    COUNT(ALUNOS.ID) AS TOTAL_ALUNOS
FROM
    ESCOLA
INNER JOIN
    PROFESSOR ON PROFESSOR.ID_ESCOLA = ESCOLA.ID_ESCOLA
INNER JOIN
    ALUNOS ON ALUNOS.ID_ESCOLA = ESCOLA.ID_ESCOLA
GROUP BY 
    NOME_ESCOLA, NOME_PROFESSOR

1

If it is not in your interest to change the data model, with Subqueries works like this:

SELECT
    e.nome_escola,
    (SELECT COUNT(p.id) FROM Professor p WHERE p.id_escola = e.id) AS professors_count,
    (SELECT COUNT(a.id) FROM Alunos a1 WHERE a1.id_escola = e.id) AS students_count,
    (SELECT COUNT(DISTINCT a2.sala_aula) FROM Alunos a2 WHERE a2.id_escola = e.id) AS rooms_count,
FROM Escola e;

Browser other questions tagged mysql sql mysqli

You are not signed in. Login or sign up in order to post.