ID-100221259Usage of COALESCE function, and detailed explanation.

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.

Advertisements

One thought on “DB2- Use of ‘COALESCE’ Function (2 of 2)

Comments are closed.