inner join with count and / or sum in mysql

Question

inner join with count and / or sum in mysql

Navigation

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

0

en mysql I have two tables, "students" and "tutors", where a tutor has several students, and each tutor has an area assigned (mathematics, Spanish, English, etc.)

What I need is a sentence to count the number of students who have tutoring with each area, for example (according to the example data of the image):

math: 8 (since the students in tutor 1 and tutor 4 are counted in math) Spanish: 4 (since only tutor 2 students study Spanish)

and so for each subject, I tried but I have not worked an inner join with count and / or sum, I thank you very much because it is very important to me, thank you very much

mysql

asked by SabanaSabrosa 13.02.2018 в 04:04

source

2 answers

0

Make a count, on the student fields once the join with tutors is done, and add at the end of your SQL statement group by area .

answered by 13.02.2018 в 04:15

download a file how can I make a $ pull on a sub-element of an array in mongodb

score 0 · Accepted Answer

This should solve your question:

SELECT T.area, 
       IFNULL(COUNT(E.nombre_estudiante),0) 'Cantidad'
       FROM tutores T
       LEFT JOIN estudiantes E
           ON E.cedula_tutor = T.cedula_tutor
       GROUP BY T.area

Basically, we recover the tutores grouping by the area of these and by means of LEFT JOIN we count the students of each group. The idea of LEFT instead of INNER is also able to see also those area that did not have students, in these cases the COUNT return NULL so if that is the case, with IFNULL() we return a 0 instead of a NULL