[SalesForce] How to you aggregate a checkbox field with SOQL

I need to get the average completion of a task which is recorded using a checkbox field. Since that is stored as TRUE and FALSE instead of 1 and 0 it's proving a bit difficult.

I would have liked to do something like this to get the average that the checkbox was checked per person:

SELECT OwnerId, AVG(MyCheckbox__c) FROM Object GROUP BY ROLLUP(OwnerId) ORDER BY AVG(MyCheckbox__c)

This obviously doesn't work because checkbox fields don't support aggregates.

Are there any options that don't involve making a duplicate field to store the same value in numeric format? I have a hard time believing this isn't a more common issue or that there isn't an approach that I'm just missing.

Best Answer

You'll need to calculate the average manually. You'll want to group the data like this:

SELECT Count(ID), OwnerId, MyCheckbox__c FROM Object__c GROUP BY OwnerID, MyCheckbox__c

You could map the averages like this:

class Average {
    Integer yes = 0, no = 0;
    Decimal avg { get { return no == 0? 0: yes/no } }
}

void calcAvg(Set<Id> userIds) {
     Map<Id, Average> averages = new Map<Id, Average>();
     for(Id userId: userIds) {
         averages.put(userId, new Average());
     }
     for(AggregateResult result:[SELECT ... ]) {
         if(result.get('expr2').equals(true)) {
             averages.get((Id)result.get('expr1')).yes = (Decimal)result.get('expr0');
         } else {
             averages.get((Id)result.get('expr1')).no = (Decimal)result.get('expr0');
         }
     }

At this point, you'll have your averages expressed as a standard decimal value in your map.

Related Topic