4

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.

3

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.

| improve this answer | |
  • I realize there are options outside of SOQL, I'm trying to find a solution that can be entirely contained within a single SOQL statement. – Ryan Elkins Dec 11 '14 at 18:35
  • @RyanElkins The only other "solution" is to write a formula field: IF(MyCheckbox__c, 1, 0), then apply an aggregate to that. Unfortunately, SOQL is not SQL, and you will find that things that would be trivial in SQL are impossible in SOQL. It's not meant to be as powerful as SQL. – sfdcfox Dec 11 '14 at 18:43
  • Yeah, I was thinking that was probably the case, just thought I'd check to see if there was a better way to handle that. – Ryan Elkins Dec 11 '14 at 18:47
2

I was able to achieve this by creating a Formula Field to translate the Checkbox field into a Number field, then the SOQL worked as expected.

enter image description here

For the following records...

enter image description here

I got these results...

enter image description here

| improve this answer | |

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.