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 -
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
+ HOUR_15 + HOUR_16)/9 AS AVERAGE_HOUR
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;