MySql - average each rows

Question

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 1

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;

Answer 2

?

Select id, id_user, 
 (q1+q2+q3) / 3.0 Avg
From table