- Salesforce Development Posts by Matt Lacey

Aggregate SOQL, When SUM (and friends!) Return Null

Posted 2011-01-24

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.get('Score') (where 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