Aggregate SOQL, When SUM (and friends!) Return Null
A quick snippet for those using aggregate SOQL queries: be aware that using
SUM on a field which may be
null in some of the included records will return
null as the value – I'm aware this is a simple issue but this post might save somebody somewhere two minutes of head scratching.
For instance, if we have an Object (
Object__c) with a number field called
Score__c and we have 3 records with the values of 2, 4 and
null in that field and run this query:
select SUM(Score__c) Score from Object__c
and then use
ar is an aggregate result, see my post on Aggregate SOQL) we'll be given
null as the sum, and chances are this isn't the desired result. Simply make sure that
null values aren't included in the total by adding a where clause to the query as below, et voilà! – we'd then get back 6 as our sum.
select SUM(Score__c) Score from Object__c where Score__c != null