Michal Nichta Michal Nichta - 5 months ago 7
SQL Question

SQL (MySQL) Count average attributable to a one person for each class

Hello there. I would ask for help with my problem. I have a modeling situation like this: I have two tables:

STUDENTS

ID, FIRST_NAME, LAST_NAME,DATE_OF_BIRTH, CLASS,
1, Petr, Fish, 4.3.1984, 4A
2, John, Stone, 5.5.1990, 4A
3, Jack, Clark, 6.3.1983, 4A
4, George, Field, 5.8.1955, 4A
5, John, Rock, 6.1.1999, 4B
6, Patrick, Light, 1.11.1983, 4B


and

POINTS_MATH:

STUDENT_ID, POINTS
1, 15
1, 20
2, 30
3, 11
3, 5
4, 3


And I need to count average of points attributable to one student in each class (also with null value), so the result may look like this:

CLASS, AVERAGE_POINTS
4A, 21
4B, 0


Thanks a lot for every answer

Answer

Based on your example you don't want the average points, but the sum of all points divided by the number of students:

 SELECT a.CLASS,
   COALESCE(SUM(p.POINTS) / COUNT(DISTINCT p.STUDENT_ID), 0)
FROM STUDENTS AS s
LEFT JOIN POINTS_MATH AS p ON s.ID = p.STUDENT_ID
GROUP BY c.CLASS

You might also check if the average of the average per student better fits your need:

SELECT s.CLASS, COALESCE(AVG(avgPoints), 0)
FROM STUDENTS AS s
LEFT JOIN 
 (
   SELECT STUDENT_ID, AVG(POINTS) AS avgPoints
   FROM POINTS_MATH
   GROUP BY STUDENT_ID
 ) AS p
ON s.ID = p.STUDENT_ID
GROUP BY s.CLASS