Michal Nichta - 3 months ago 5

SQL Question

*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

POINTS_MATH:

`STUDENT_ID, POINTS`

1, 15

1, 20

2, 30

3, 11

3, 5

4, 3

`CLASS, AVERAGE_POINTS`

4A, 21

4B, 0

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
```

Source (Stackoverflow)

Comments