Sterica Capatina Sterica Capatina - 1 month ago 15
MySQL Question

MySql - average each rows

I have this table:



+----+---------+----+----+----+
| ID | ID_USER | Q1 | Q2 | Q3 |
+----+---------+----+----+----+
| 1 | 31 | 3 | 4 | 5 |
| 2 | 2 | 5 | 5 | 8 |
| 3 | 5 | 6 | 2 | 3 |
+----+---------+----+----+----+





How can average for each id_user. To have a table of this type:



+----+---------+-----+
| ID | ID_USER | AVG |
+----+---------+-----+
| 1 | 31 | 4 |
| 2 | 2 | 6 |
| 3 | 5 | 5.5 |
+----+---------+-----+




Thank you!

Answer

The simple answer is:

select id, id_user, (q1 + q2 + q3) / 3 as average
from t;

However, if not all questions have values (say they are NULL) and you want to include the values, then you need ore complex logic:

select id, id_user,
       (coalesce(q1, 0) + coalesce(q2, 0) + coalesce(q3, 0)) /
        nullif( (q1 is not null) + (q1 is not null) + (q3 is not null)), 0)
       ) as average
from t;