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


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.


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

2 Answers 2


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) {
        if (counselor.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 
  • His question is bit different Commented Jul 15, 2016 at 21:17
  • If SOQL doesn't support it directly then I'll do something different. Thanks
    – Matt M
    Commented Jul 15, 2016 at 21:27
  • @llyaLepesh COUNT will return wrong output Commented Jul 17, 2016 at 4:12

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

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 

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

You must log in to answer this question.

Not the answer you're looking for? Browse other questions tagged .