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

Instruction

Okay! That was AVG(COALESCE(x,0)). Now, you may wonder what happens when we swap the functions:

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

Will the result be the same? No, it won't.

This query will compute the average for all the projects ignoring NULL values. In case all the values are NULL and so the average is NULL too, the COALESCE function will replace the NULL average with a zero.

Exercise

Let's check that in practice. For each translator, show their id and the average number of words per project.

Exclude project_id = 13 so that we can see what happens when the average is NULL. If the average turns out to be NULL, it means that the translator didn't translate anything yet.

Console

Code editor

Result

TableConsole