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;
- 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.