SQL avg() function with null values

The SQL AVG() function is used to calculate the average of a set of numeric values in a column. When there are no null values in the column, the AVG() function works as expected and returns the arithmetic mean of the values. However, when the column contains null values, the behavior of AVG() can be different depending on the database management system being used.

Some database systems treat null values as zero when calculating the average, while others exclude them from the calculation.

To handle null values in the AVG() function, you can use the COALESCE() function to convert null values to a non-null value. For example, you can use COALESCE(column_name, 0) to convert null values to 0, so that they are included in the calculation of the average.

If the column contains NULL values, the AVG function will still calculate the average but will exclude the NULL values from the calculation.

Example 1: Using AVG with NULL values in a single column

Here is the table:

table: student_score

To retrieve the average score achieved by students we can use the following query.

Code:

SELECT AVG(score_achieved) FROM student_score;

Output:

avg                 |
--------------------+
170.2500000000000000|

This query will return the average score achieved by all students in the student_score table, excluding the NULL values.

In this case, the sum of score achieved is 681 (150+174+185+172+NULL+NULL), and the count of non NULL values is 4 (150, 174, 185, and 172). Therefore, the average score achieved is (681/4) = 170.2500000000000000.

To include the NULL values in the calculation of AVG, we can use the COALESCE function to replace the NULL values with a default value (such as 0) before applying AVG.

The COALESCE function returns the first non-null argument, so COALESCE(score_achieved,0) will return score_achieved if it is not NULL, and 0 otherwise.

In the above table the column score_achieved contain NULL values. To find the average score_achieved, we can use the following query:

Code:

SELECT AVG(COALESCE(score_achieved,0)) FROM student_score;

Output:

avg                 |
--------------------+
113.5000000000000000|

This is because the average of the scores (150, 174, 0, 185, 0, and 172) with missing values treated as 0 is 113.5. It is worth noting that treating the missing values as 0 might not be appropriate in all cases, as it can artificially lower the average score.

Example 2: Using AVG NULL values in multiple columns:

Here is the table:

sample table: student_score

Suppose we want to get the average score_achieved for each student, including those with NULL score_achieved. We can use the following query:

Code:

SELECT student_id,AVG(COALESCE(score_achieved,0)) as AvgScoreGrp
FROM student_score
group by student_id
order by AvgScoreGrp

Output:

student_id|avgscoregrp         |
----------+--------------------+
         3| 87.5000000000000000|
         5| 89.5000000000000000|
         1|150.0000000000000000|
         6|172.0000000000000000|
         2|174.0000000000000000|
         4|185.0000000000000000|

The query will return the average score achieved by each student in the student_score table, treating any missing values in the score_achieved column as 0. The COALESCE function returns the first non-null argument, so COALESCE(score_achieved,0) will return score_achieved if it is not NULL, and 0 otherwise.

Check out our 1000+ SQL Exercises with solution and explanation to improve your skills.

Previous: Avg Functions
Next: Max Function