GeekInDisguise GeekInDisguise - 1 year ago 43
SQL Question

Max Of Average - MYSQL

I am trying to repeat what I have done on excel in mysql.

Averaging multiple columns and getting the highest/max value of this calculation.

Here is the data and that I have worked out in excel -

enter image description here

The values in orange is excels Average function and the green is excels Max function.

I am using the following SQL to get the same results in MYSQL.

SELECT AVG(HOUR_8 + HOUR_9 + HOUR_10 + HOUR_11 + HOUR_12 + HOUR_13 + HOUR_14

But the Best average that is output is Now if i carry out the same equation in mysql the Best average is 13.51851852

I have tried to combine the sql with MAX but it simply doesn't allow you to do that.

Is there any other way of repeating what I have done in excel in MYSQL correctly?

Answer Source

You don't need the AVG() function. That is an aggregation function and all the values are in a single row.

Instead, you can just calculate the value arithmetically. Then, you can use ORDER BY and LIMIT, or just MAX() to get the value:

select t.*,
       ((hour_8 + hour_9 + hour_10 + hour_11 + hour_12 + hour_13 + hour_14 + hour_15 + hour_16)/9) as avg_hour
from t
order by avg_hour desc
limit 1;

This gives all the other values in the row. If you just want the maximum average:

select max((hour_8 + hour_9 + hour_10 + hour_11 + hour_12 + hour_13 + hour_14 + hour_15 + hour_16)/9) as max_avg_hour
from t;