GeekInDisguise - 6 months ago 21

SQL Question

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

FROM HOURS

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

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

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;
```