How to get the sum of three tables in sql

Question

How to get the sum of three tables in sql

Navigation

#1 by (1 votes)
#2 by (1 votes)

3

I have 3 Tables, which are:

School: id , id_escola and nome_escola ;
Teacher: id , id_escola and nome_professor ;
Students: id , id_escola , sala_aula and numero_alunos ;

I am aware of joins , count 's, and sum ' s but I am not able to mount SQL .

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

How to mount this query?

mysql sql mysqli

asked by anonymous 30.07.2015 / 16:55

2 answers

C # MVC5 - Table with CheckBox and Different Actions Change file extension in C #

score 1 · Answer 1

Try this query. But so ... you can improve the structure of your tables.

The school table does not need two primary keys unless it is necessary.
In the students table you do not need the numero_alunos column.

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

score 1 · Answer 2

If it is not in your interest to change the data model, 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;