💻 Tech
Suppose you have the two tables below, and you need to print the student id, total points, and the type.
student table
| id | name |
| --- | ---- |
| 1 | John |
| 2 | Jane |
score table
| id | points | student_id | type |
| --- | ------ | ---------- | ---- |
| 1 | 1 | 1 | |
| 2 | 2 | 1 | |
| 3 | 3 | 1 | |
| 4 | 4 | 2 | quiz |
| 5 | 5 | 2 | |
| 6 | 6 | 2 | |SELECT st.id, SUM(sc.points), sc.type
FROM student st
JOIN score sc ON sc.student_id = st.ID
GROUP BY st.id, sc.type
ORDER BY st.id;
-- output
-- | id | sum | type |
-- | --- | --- | ---- |
-- | 1 | 6 | |
-- | 2 | 4 | quiz |
-- | 2 | 11 | |However, the output is not what’s expected. The sum of points of student #2 should be 15, not 11. There are two rows for student #2 because there are two type values: quiz and NULL. What needs to be achieved is to combine them. To do that, you can remove the type from GROUP BY clause and use MAX().
SELECT st.id, SUM(sc.points), MAX(sc.type)
FROM student st
JOIN score sc ON sc.student_id = st.ID
GROUP BY st.id
ORDER BY st.id;
-- output
-- | id | sum | max |
-- | --- | --- | ---- |
-- | 1 | 6 | |
-- | 2 | 15 | quiz |MAX() gets the maximum value of the column. In this case, it gets the maximum value of the type column. Since quiz is greater than NULL, it returns quiz. If there are multiple non-null values, it will return the maximum value of the non-null values. However, take note that this may still return multiple rows. The given scenario is only when there are two possible values for the type column.