DB2- Use of ‘COALESCE’ Function (2 of 2)

Usage of COALESCE function, and detailed explanation given in this post. The given example helps you to handle NULL values in SQL rows returned from the table.

Syntax for COALESCE function

SELECT bird_name, AVG(COALESCE(found_tally, 0))
FROM Samples AS SA
LEFT OUTER JOIN
SampleGroups AS SG
ON SA.sample_id = SG.sample_id
AND SA.bird_name = :my_bird_name
AND group_id = :my_group
GROUP BY bird_name;

Explanation

• The COALESCE function will inspect its parameter list and return the first non-NULL value, so this converts the AVG() parameter from NULL to zero. Most people seem to have trouble with the idea that an aggregate can handle an expression, not just a single column, as a parameter.
• The other good trick in this solution is doing a LEFT OUTER JOIN on two columns instead of just one. This is very handy because the primary key of a table is not always just one column.