Apex Code Development You need to sign in to do that
Don't have an account?
Siva 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
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
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.
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,
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.
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;
}
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')); }Is this issue resolved or you are still facing any issue on this?
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.