CODEWITHSUNDEEP
mysql
Comy
Comy

Reputation: 91

Multiple JOINS - Mysql

I have 4 tables:

Classes:

id  class   
1   X C     
2   VI B    
3   XII F   
4   X C     
5   VI B    
6   VIII A

Students:

    id  id_student  class   
    2   1 [->]      X C     
    3   4 [->]      VI B    
    5   7 [->]      X C

Grades

Id  id_student  grade   
3   1 [->]      8       
4   1 [->]      7       
5   1 [->]      10      
6   1 [->]      8       
7   1 [->]      9       
8   4 [->]      5       
9   4 [->]      7       
10  4 [->]      9   
11  1 [->]      4       
12  1 [->]      9       
13  7 [->]      10

Absences:

id  id_student
1   1 [->]              
2   1 [->]              
3   1 [->]              
4   4 [->]              
5   1 [->]              
6   1 [->]              
7   1 [->]              
8   7 [->]

What I want is to select all classes from Classes Table (only those who have students) and count the number of students for every class, the average of their grades and number of absences.

This is what I have:

SELECT classes.class, COUNT(distinct students.id_students) AS nr_students, AVG(grades.grade) AS avg_grades
FROM classes 
    INNER JOIN students ON students.student = clases.class
    INNER JOIN grades ON students.students = grades.id_student
GROUP BY class

Until now everything is great. Output (PHP):

Class  Nr_Students  Avg_Grades
VI B   1           7.0000    
X C    2           8.1250

But I want to count every row from Absences table and print the total number of absences for every class.

This is what I'm trying to do:

Class  Nr_Students  Avg_Grades  Nr_Abcences
VI B   1           7.0000       1
X C    2           8.1250       7

I can't change the DB!

Thank you.

Upvotes: 2

Views: 52

Answers (2)

OGHaza
OGHaza

Reputation: 4795

I suspect that some of the column and table names will need to be changed, since your query doesn't match your schema at all. The following works though:

SELECT s.class
    ,COUNT(s.id_student) AS nr_students
    ,g.avg_grade AS avg_grades
    ,SUM(a.cnt_absences) AS absences
FROM students s 
INNER JOIN (
    SELECT s.class, AVG(g.grade) AS avg_grade 
    FROM grades g
    INNER JOIN students s ON g.id_student = s.id_student
    GROUP BY s.class
  ) g ON s.class = g.class
LEFT JOIN (
    SELECT id_student, COUNT(id) AS cnt_absences 
    FROM absences  
    GROUP BY id_student
  ) a ON s.id_student = a.id_student
GROUP BY s.class, g.avg_grade

See it working on SQLFiddle

Upvotes: 1

Dmitry Seleznev
Dmitry Seleznev

Reputation: 965

Only subquery is provide: 

 SELECT classes.class, COUNT(distinct students.id_students) AS nr_students, AVG(grades.grade) AS avg_grades, COALESCE(ab.Nr_Abcences, 0)
    FROM classes 
        INNER JOIN students ON students.student = clases.class
        INNER JOIN note ON students.students = grades.id_student
        LEFT JOIN (SELECT class, COUNT(ab.id) AS NR_Abcences FROM Students INNER JOIN Absences ab ON ab.id_student = Students.id_student GROUP BY Students.class) ab ON ab.class = clases.class
    GROUP BY class

I didn't test this query.

Upvotes: 0

Related Questions