Apex Code Development You need to sign in to do that
Don't have an account?
BroncoBoy SOQL Aggregate Funtion: Retrieving Count and Values
Setup:
We built a custom object where we're capturing just a few fields: type, completed_date and (associated) contact IDs for each type. Type(Type__c) can have a value of a either meetng or call. I'm tryting to retrive the number of meetings and number of calls and group those by the contact Ids using a SOQL aggregate: COUNT(fieldName). The ideal result is that we want to see that contact Joe Smith has 4 calls and 3 meetings; contact Susan Johnson has 1 call and 10 meetings.
Current Code:
String query = 'SELECT aacac.Contact_Id__c varAggContactId, COUNT(aacac.Type__c) varAggCount, aacac.Completed_Date__c FROM Accumulated_Activity__c aacac WHERE (aacac.Type__c LIKE \'%Meetng%\' OR aacac.Type__c LIKE \'%Call%\') GROUP BY aacac.Contact_Id__c, aacac.Completed_Date__c';
System.debug(query);
System.debug(Database.query(query));
//This Returns
AggregateResult:{varAggCount=4, Completed_Date__c=Thu May 30 00:00:00 GMT 2013, varAggContactId=0033000000JDZklAGH},
AggregateResult:{varAggCount=2, Completed_Date__c=Sun Jun 02 00:00:00 GMT 2013, varAggContactId=0033000000JDZklAGH},
As you can see I'm getting the number but I don't know if the 4 is the number of meetings or calls.
Question:
Any thoughts how I can get both the number of a certain type grouped by contact as well as the type value itself (4 meetings, 2 calls)?
Thank you in advance!
-BroncoBoy
Can you try this, which is grouping the results by Id:
String query = 'SELECT aacac.Contact_Id__c varAggContactId, aacac.Type__c varAggType, aacac.Completed_Date__c, COUNT(Id) varAggCount FROM Accumulated_Activity__c aacac WHERE (aacac.Type__c LIKE \'%Meeting%\' OR aacac.Type__c LIKE \'%Call%\') GROUP BY aacac.Contact_Id__c, aacac.Type__c, aacac.Completed_Date__c';
All Answers
Correction to the code I posted, there was a typo:
String query = 'SELECT aacac.Contact_Id__c varAggContactId, COUNT(aacac.Type__c) varAggCount, aacac.Completed_Date__c FROM Accumulated_Activity__c aacac WHERE (aacac.Type__c LIKE \'%Meeting%\' OR aacac.Type__c LIKE \'%Call%\') GROUP BY aacac.Contact_Id__c, aacac.Completed_Date__c';
System.debug(query);
System.debug(Database.query(query));
//This Returns
AggregateResult:{varAggCount=4, Completed_Date__c=Thu May 30 00:00:00 GMT 2013, varAggContactId=0033000000JDZklAGH},
AggregateResult:{varAggCount=2, Completed_Date__c=Sun Jun 02 00:00:00 GMT 2013, varAggContactId=0033000000JDZklAGH},
You need to group the results by ContactId, Type and Date. Try the following revised query
String query = 'SELECT aacac.Contact_Id__c varAggContactId, aacac.Type__c varAggType, COUNT(aacac.Type__c) varAggCount, aacac.Completed_Date__c FROM Accumulated_Activity__c aacac WHERE (aacac.Type__c LIKE \'%Meeting%\' OR aacac.Type__c LIKE \'%Call%\') GROUP BY aacac.Contact_Id__c, aacac.Type__c, aacac.Completed_Date__c';
System.debug(query);
System.debug(Database.query(query));
If this post solves your problem kindly mark it as solution and throw Kudos.
When I try that, I get the error: FATAL_ERROR System.QueryException: Grouped field should not be aggregated: Type__c. Thoughts?
Can you try this, which is grouping the results by Id:
String query = 'SELECT aacac.Contact_Id__c varAggContactId, aacac.Type__c varAggType, aacac.Completed_Date__c, COUNT(Id) varAggCount FROM Accumulated_Activity__c aacac WHERE (aacac.Type__c LIKE \'%Meeting%\' OR aacac.Type__c LIKE \'%Call%\') GROUP BY aacac.Contact_Id__c, aacac.Type__c, aacac.Completed_Date__c';
That worked thank you very much!
-BB