SQL Find Average Multiple Columns

I am trying to get the average of multiple columns in my SQL database. This will be used to aggregate the average score of my student’s attempts on various quizzes. This is the query I am using right now:

AVG((`2.R.1` + `2.R.2` + `2.R.3` + `3.R.1` + `3.R.2` + `3.R.5`)/6) as avg
FROM standards_data
WHERE `uuid` = 4

Here is the table structure (part cut off because of length, but you get the idea): enter image description here

When I run this query, there are no errors, but it just returns a null value. Due to the nature of this database, there will be times when a column is set to NULL (as seen in the 2.R.2 example)

Here is the outcome of the query: enter image description here

Is there a better way I need to go about doing this? I’m still learning SQL, so any table setup advice would also be appreciated.


If you are using MySQL, you can get the average — ignoring NULLs — using:

SELECT (COALESCE(`2.R.1`, 0) + COALESCE(`2.R.2`, 0) + COALESCE(`2.R.3`, 0) + COALESCE(`3.R.1`, 0) + COALESCE(`3.R.2`, 0) + COALESCE(`3.R.5`, 0)) /
       ((`2.R.1` IS NOT NULL) + (`2.R.2` IS NOT NULL) + (`2.R.3` IS NOT NULL) + (`3.R.1` IS NOT NULL) + (`3.R.2` IS NOT NULL) + (`3.R.5` IS NOT NULL)
FROM standards_data
WHERE `uuid` = 4;

This calculates the average within each row. If you actually want the average across rows as well, you can use AVG() with the above as the argument.