+ Start a Discussion
Siva SakthiSiva Sakthi 

How to aggregate the for Check box Value in SOQL

Hi,

          I have to calculate the aggregate for check box value via SOQL.? How to get the aggregate and sum the check box value aggregate.My requirement is Event based attenance like check box value is true means present other wise appesant . Have to calculate the aggregate of present /not present. I am getting the Count value from query , how to pass this value and get the aggregate to calcualte the Attendance percentage (100%).
Please guide me to solve this issue ASAP and share sample coding for this. 

My Sample Code Below:
public String getAttendance(string Subjectcode) {          
        Decimal SubAttendance= 0;        
        EmpNo = string.escapesinglequotes(EmpNo);  
        List<AggregateResult> aggregatelist = new List<AggregateResult>();         
        aggregatelist = [Select Count(Id) cont,Title__c Title,Subject_Code__c,Present__c Present  from Attendance_Table__c where Type__c='Event' And Emp_No__c =:EmpNo And Code__c ='E-11' Group By Title__c,Code__c,Present__c ];        
        map<string,map<boolean,string>> mapnew = new map<string,map<boolean,string>>();
         for(Aggregateresult temp:aggregatelist){
            map<boolean,string> mapinst = new map<boolean,string>();
            mapinst.put(temp.get('Present'),string.valueof(temp.get('cont')));
            mapnew.put(string.valueof(temp.get('Title')),mapinst);
        }
         System.debug('Boolean Result' + mapnew);
          for(string temp:mapnew.keyset()){
              wrapperlist.add(new wrapperinstance(temp,mapnew.get(temp).get(true)/(mapnew.get(temp).get(true) + mapnew.get(temp).get(false))));
          }
        return String.valueOf(((SubAttendance/500)*5));   
    } 

Advance Thanks 
Maheshwar
Rahul Sangwan7341Rahul Sangwan7341
Hi Maheshwar,

You can get this simply by tracersing the list:

1) Get all the records from which you have to check absent or presentee and store the count in 1 variable.
2) Take 2 variables(Integer) one for calculating absentee and 1 for presentee.
3) Traverse the list which you got from step 1 and check whether checkbox is checked or not. If checked increase the presentee by 1 and if unchecked increase absentee by 1.
4) by the end of traversing a list you will get Total number of students, total present, total absentee. Now you can calculate the %age presentee*100/total students.

Please mark this as Best Answer if it helps you.

Let me know if you are still facing any problem.
 
Siva SakthiSiva Sakthi
Thanks Rahul. If Possible can you update/ send the code to me .
Rahul Sangwan7341Rahul Sangwan7341
Is this is the query which will give total records need to check:
List<AggregateResult> aggregatelist = new List<AggregateResult>();         
        aggregatelist = [Select Count(Id) cont,Title__c Title,Subject_Code__c,Present__c Present  from Attendance_Table__c where Type__c='Event' And Emp_No__c =:EmpNo And Code__c ='E-11' Group By Title__c,Code__c,Present__c ]; 


and can you tell me which field is the checkbox which stores the present or not value,
Siva SakthiSiva Sakthi
Present__c Value is the check box value
 
Rahul Sangwan7341Rahul Sangwan7341
List<Attendance_Table__c> aggregatelist = new List<Attendance_Table__c>();         
        aggregatelist = [Select Id,Title__c Title,Subject_Code__c,Present__c Present  from Attendance_Table__c where Type__c='Event' And Emp_No__c =:EmpNo And Code__c ='E-11' Group By Title__c,Code__c,Present__c ];

Integer total = aggregatelist.size();
Integer totalPresentee = 0;
Integer totalAbsentee = 0;
for(Attendance_Table__c attObj : aggregatelist){
	if(attObj.Present__c)
		totalPresentee = totalPresentee + 1;
	else
		totalAbsentee = totalAbsentee + 1;
}	

Decimal presentPercent = totalPresentee * 100/total;
Decimal absentPercent  = totalAbsentee * 100/total;

Please mark this as Best Answer if it helps you.

Let me know if you are still facing any problem.
 
Siva SakthiSiva Sakthi
Thanks For your Reply.

Actually we get the Group By value means We have to use the 
List<AggregateResult> aggregatelist = new List<AggregateResult>();  right.  I am getting the error in the If condition after for loop.Error:
Error: Loop variable must be of type AggregateResult . Given the 'AggregateResult' in for loop means causing error like   
Error: Invalid field Present__c for SObject AggregateResult at line 108 column 14

for(Attendance_Table__c attObj : aggregatelist){
          if(attObj.Present__c)
             totalPresentee = totalPresentee + 1;
           else
             totalAbsentee = totalAbsentee + 1;
       }
Rahul Sangwan7341Rahul Sangwan7341
Hi Maheshwar, 

Can you refer the below code. I am sure it will helps you. Please let me know if there is any issue.
 
List<aggregateResult> results = [select  count(Id) total, Test__c  
                                 from Account group by Test__c ];
for(aggregateResult agr : results){
    if(agr.get('test__c') == true)
        system.debug('True::::'+ agr.get('total'));
        else
        system.debug('False::::'+ agr.get('total'));    
    }

 
Rahul Sangwan7341Rahul Sangwan7341
Hi Maheshwar,

Is this issue resolved or you are still facing any issue on this?
Siva SakthiSiva Sakthi
Thanks Rahul,

 This issue has been resolved with another fromat like get over all check box aggregate in one query and another one query check box true values. Then calculate these two query results. thats it.