Introduction
Function AVG
21. AVG with COALESCE
Functions SUM, MAX, MIN
Revision

Instruction

Good. Remember: if all arguments are NULL, you will get NULL as the average. Makes sense.

Of course, you can count NULLs as zero, too. Here is how. Remember, the function COALESCE(x,y)? We can use it to replace x with y if x is NULL:

SELECT AVG(COALESCE(price,0))
FROM project;

The above query will find the average price for all projects. If a project has a NULL price, it will indeed be treated as project with the price 0.

Exercise

Find the average number of words per project translated so far by Adriana Fuentes (translator_id = 5). Assume that NULL in column words means that nothing has been translated yet.

Stuck? Here's a hint!

Use AVG(COALESCE(words,0)).

Console

Code editor

Result

TableConsole