This is the solution to the last SQL exercise from the series covering the students/disciplines field.
The Third one from the series featured a WITH AS and GROUP BY clause, today we are working more on SQL aggregations such as :
You should get the 3 tables (student, discipline, score) with data in them.
Today we use Mysql 8.0.22 version instead of Postgresql. If you have any trouble using any SQL server, don’t hesitate to drop us a comment.
Here is what you should obtain as a result without taking care of coefficients and scale :
And now the query :
SELECT round(AVG(score),2) as average, MAX(score), MIN(score), student.name FROM student INNER JOIN score ON score.student_id = student.id GROUP BY student_id ORDER BY average DESC;
Nothing fancy here, the only particularity is the alias added to the average function on which is computed a round calculation.
The ORDER BY clause then uses the alias.
Adding the coefficient will alter a bit the result :
SELECT round(AVG(score * coefficient),2) as average, MAX(score), MIN(score), student.name FROM student INNER JOIN score ON score.student_id = student.id INNER JOIN discipline ON discipline.id = score.discipline_id GROUP BY student_id ORDER BY average DESC;
The coefficient adds some value to some of the disciplines, the average score is a bit altered.
At first sight we can not do much to optimize the performance of our query as the student id on which we group by is already an index key.
However we are having an ORDER BY clause here, but it is set on an aggregate function so how could we create an index on that and if possible, is it good practice ?
Index on aggregate function with SQL
When you start thinking about this, you should end up saying :
“What the heck is that ! You wanna build an index on an aggregate function which needs to process on all rows of the table ??”
And you will be right, that’s non sense as we need to browse all result sets from the score table and apply the aggregate average function.
There could be one way to help in case of high process volume using Materialized views (not available in Mysql but workarounds exist) and creating an index out of it. Nevertheless you should consider the real benefit before using it.
We will keep the query without index modification :).
Stay tuned for the next exercise on SQL and table relationships.