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