Solution to SQL Aggregation exercise

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 :

• MIN
• MAX
• AVG

Same Context

Solution

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.

Query Optimization

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.

Conclusion :

We will keep the query without index modification :).

Stay tuned for the next exercise on SQL and table relationships.