3

Need some SOQL help. How can I get back a nice list of counts for each checkbox fields.

Example:

Select CheckField1,CheckField2 FROM Account_Counselor WHERE User = '123..'

This will currently give me three rows with T/F for each field.

I want 1 row with a count of true values.

Like:

User CheckField1   CheckField2
123       1          (blank)
  • Do want to both the CheckField1,CheckField2 are true for those accounts which will be listed as results? – Santanu Boral Jul 15 '16 at 22:04
3

checkboxes don't support aggregate functions, try this:

integer CheckField1 = 0;
integer CheckField2 = 0;

for (Account_Counselor counselor :[Select CheckField1,CheckField2 FROM Account_Counselor WHERE User = '123..']){
    if (counselor.CheckField1) {
        CheckField1++;
    }
        if (counselor.CheckField2) {
        CheckField2++;
    }
}

system.debug('123..: CheckField1: ' + CheckField1 + ' CheckField2: ' + CheckField2);

Or you can create formula field formulaCheck1 (return number):

if (CheckField1__c, 1, 0)

And aggregate by it:

select sum(formulaCheck1__c) from Account_Counselor where User = '123...' group by User 
|improve this answer|||||
  • His question is bit different – Santanu Boral Jul 15 '16 at 21:17
  • If SOQL doesn't support it directly then I'll do something different. Thanks – Matt M Jul 15 '16 at 21:27
  • @llyaLepesh COUNT will return wrong output – Santanu Boral Jul 17 '16 at 4:12
1

Create 2 formula fields formulaCheck1__c and formulaCheck2__c like this:

formulaCheck1__c = if (CheckField1__c, 1, 0)

formulaCheck2__c = if (CheckField2__c, 1, 0)

And perform the SOQL query as follows:

SELECT User,SUM(formulaCheck1__c), SUM(formulaCheck2__c) FROM Account_Counselor 
WHERE User = '123..'
GROUP BY User

It will give you desired result.

User CheckField1   CheckField2
 123       4          3 

If you do not want to use formula field then use this type of query:

SELECT Check1__c, Check2__c,GROUPING(Check1__c) grpCheck1, GROUPING(Check2__c) grpCheck2,
COUNT(id) accts
FROM Account_Counselor 
WHERE User = '123..' 
GROUP BY CUBE(Check1__c, Check2__c)
HAVING GROUPING(Check1__c) = 1 OR GROUPING(Check2__c) = 1
ORDER BY COUNT(id), Check1__c, Check2__c 
LIMIT 2

It will give the results as shown in picture:

enter image description here

You can find the check2 field count is 3 and check1 field count is 4 as I have shown earlier.

For more information on how GROUP BY CUBE works, visit this documentation GROUP BY CUBE

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