SOQL Aggregate Results

on

Querying Aggregate Results in SOQL

I recently created a Visualforce page for a customer that needed to query and then total up some activities from the Task table in Salesforce.

When building something like this in the past I would typically:
  1. Query for the records I need
  2. Loop through them and add up all of the variables
  3. Display the calculated variables in the resulting table
However, after a bit of swatting up on Aggregate Results in SOQL I realised that in many of the occasions in the past, I could have actually retrieved the value I needed directly within the Query, without having to loop through a set of results.




I'll put the full code and page code below, but here's the Aggregate Query I created with the aggregated (bits) in bold.

AggregateResult[] results = [
            SELECT Owner.Name , Manager__c,  Sum(CallTalkTimeInSeconds__c) talkTime, CallType, Count(Id) totalCalls FROM Task 
            where CallTalkTimeInSeconds__c != null AND Manager__c !=null
            group by Manager__c, Owner.Name, CallType
            order by Manager__c, Owner.Name
        ];

Lessons Learnt

When setting up this query I hit a few new error messages in the Query Editor.

I hadn't come across them before so here's a brief explanation and I'll do more detailed links below.

"Field cannot be cannot be grouped by in a query call"

This error showed up when I tried to get the SOQL to group my results by a field that was Formula Type - apparently, you can't do this - there's a full list of fields that you can/cannot group here: https://developer.salesforce.com/docs/atlas.en-us.soql_sosl.meta/soql_sosl/sforce_api_calls_soql_select_agg_functions_field_types.htm

"Field must be grouped or aggregated: Id" 

This error drove me crazy for a while. Each time I'd modify the query, the field name showing in the error would change. It seemed like I did every combination. In summary though, as soon as you start to use Aggregate functions like SUM you have to specify what happens to all of the columns in the query. 

Page Output


ManagerEmployeeType of CallNumber of CallsTime (Seconds)Time (HH:MM:SS)
Fred's Manager
Fred
Inbound
100
60
00:01:00

Further Reading



Controller

public with sharing class TestController {
    
    public Summary[] Summaries { get; set; }
    
    public TestController() {
        AggregateResult[] results = [
            SELECT Owner.Name , Manager__c,  Sum(CallTalkTimeInSeconds__c) talkTime, CallType, Count(Id) totalCalls FROM Task 
            where CallTalkTimeInSeconds__c != null AND Manager__c !=null
            group by Manager__c, Owner.Name, CallType 
            order by Manager__c, Owner.Name
        ];
        Summaries = new List<Summary>();
        for (AggregateResult ar : results) {
            Summaries.add(new Summary(ar));
        }
    }
    
    // wrapper class to hold aggregate data
    public class Summary {
        public String ManagerName { get; private set; }
        public Integer TotalCalls { get; private set; }
        public Decimal TalkTime { get; private set; }
        public String TalkTimeHHMMSS { get; private set; }
        public String Name { get; private set; }
        public String CallType { get; private set; }
        
        
        public Summary(AggregateResult ar) {
            TotalCalls = (Integer) ar.get('totalCalls');            
            TalkTime = (Decimal) ar.get('talkTime');
            Integer times = TalkTime.intValue();
            dateTime dt = DateTime.newInstance(0);
            system.debug(dt.addseconds(times).formatGMT('HH:mm:ss'));
            TalkTimeHHMMSS = dt.addseconds(times).formatGMT('HH:mm:ss');
            Name = (String) ar.get('Name');
            ManagerName = (String) ar.get('Manager__c');
            CallType = (String) ar.get('CallType');
            
        }
    }
    
}


Full Page Code

<apex:page controller="TestController" tabStyle="Lead">
    
    <p><b>Call Durations</b> <br/>
        
    </p>
    <table border="1" class="list">
        <apex:variable value="{!1}" var="i"/>
        <th>Manager Name</th>
        <th>BDC Name</th>
        <th>CallType</th>
        <th>Number of Calls</th>
        <th>Time (Seconds)</th>
        <th>Time (HH:MM:SS)</th>
        
    <apex:repeat value="{!Summaries}" var="summary"> 
        <tr style="background-color:{!IF(mod(i,2)==0, 'white', 'lightgray')};" class="dataRow">
 
            <td>{!summary.ManagerName}</td>
            <td>{!summary.Name}</td>
            <td>{!summary.CallType} </td>
            <td>{!summary.TotalCalls}</td>              
            <td>{!summary.TalkTime}</td>
            <td>{!summary.TalkTimeHHMMSS}</td>
            
        </tr>
        <apex:variable value="{!i+1}" var="i" />
    </apex:repeat>
</table>
    <p>
        Detailed Report
    </p>
<analytics:reportChart reportId="00OD0000006wCf3" >
  </analytics:reportChart>
    

</apex:page>

Photo credit: Pixabay on Pexels

Comments

Post a Comment