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

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

SELECT bird_name, AVG(COALESCE(found_tally, 0))
FROM Samples AS SA
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;


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.


Author: Srini

Experienced software developer. Skills in Development, Coding, Testing and Debugging. Good Data analytic skills (Data Warehousing and BI). Also skills in Mainframe.

Comments are closed.