SOQL Group By Clause

GROUP BY clause is used in SOQL query to group set of records by the values specified in the field. We can perform aggregate functions using GROUP BY clause.

Aggregated functions for GROUP BY clause:

  • COUNT ()
  • COUNT (FIELD_NAME)
  • COUNT_DISTINCT ()
  • SUM ()
  • MIN ()
  • MAX ()

Example:

SELECT Industry, COUNT(Id) From Account GROUP BY Industry

GROUP BY With HAVING Clause:
GROUP BY HAVING Clause is used in SOQL to apply a condition based on a group field values.

Example:

SELECT Industry, COUNT(Id) From Account GROUP BY Industry HAVING Industry IN ('Agriculture','Manufacturing','Construction')

GROUP BY ROLLUP Clause:
GROUP BY ROLLUP Clause is used to add subtotals to get aggregates data in the query results. It returns multiple levels of subtotal rows. We can add up to three fields in a comma-separated list in GROUP BY ROLLUP Clause statement.

Example:

SELECT Industry, Type, COUNT(Id) From Account GROUP BY ROLLUP (Industry, Type)

GROUP BY CUBE Clause:
GROUP BY CUBE clause is used in SOQL query to add subtotals for all combinations of a grouped field in the query results.

Example:
The following query returns subtotals of accounts for each combination of Type and BillingCountry.

SELECT Type, BillingCountry,
GROUPING(Type) grpType, GROUPING(BillingCountry) grpCity,
COUNT(Id) accnts
FROM Account
GROUP BY CUBE(Type, BillingCountry)
ORDER BY GROUPING(Type), GROUPING(BillingCountry)

Some object fields have a field type that does not support grouping. You can’t include fields with these field types in a GROUP BY clause.

Following are the list of Groupable & Non-Groupable field types:

Groupable Field Types:

  • Id (Id)
  • Lookup (Id)
  • Checkbox (Boolean)
  • Phone (String)
  • Picklist (String)
  • Email (String)
  • Text (String)
  • Text Area (String)
  • URL (String)
  • Number (Int). Does not include custom fields, only standard Number fields with SOAP type int, like Account.NumberOfEmployees.
  • Date (date)
  • Direct cross-object references to groupable fields, up to 5 levels from the root object (SOQL limit), as in SELECT count(Id) FROM Contact GROUP BY Account.Parent.Parent.Parent.Parent.Name. Both custom and standard references are groupable.
  • Formulas of type Checkbox and Date, including cross-object formulas across standard and custom relationships.

Non-Groupable Field Types:

  • Auto Number (string)
  • Address Compound Fields
  • Number (double), including custom Number fields with or without decimal and regardless of scale.
  • Percent (double), including custom Percent fields with or without decimal and regardless of scale.
  • Currency (double), including custom Currency fields with or without decimal and regardless of scale.
  • Components of Address compound fields are groupable if their types otherwise allow it.
  • Geolocations, both custom and standard, and whether or not defined as having decimal places, including the compound field and components (location/double)
  • Long Text (string)
  • Rich Text (string)
  • Multi-Select Picklist (string)
  • Roll-Up Summary Fields (double), including COUNT rollups.
  • Encrypted Text Fields (Classic Encryption; string)
  • Date/Time (dateTime)
  • Time (time)
  • Formulas of types other than Checkbox and Date, including the otherwise-groupable String type.

Aggregate Functions Supported Field Types:

Data Type AVG() COUNT() COUNT_DISTINCT() MIN() MAX() SUM()
base64 No No No No No No
boolean No No No No No No
byte No No No No No No
date No Yes Yes Yes Yes No
dateTime No Yes Yes Yes Yes No
double Yes Yes Yes Yes Yes Yes
int Yes Yes Yes Yes Yes Yes
string No Yes Yes Yes Yes No
time No No No No No No
address No No No No No No
anyType No No No No No No
calculated Depends on data type* Depends on data type* Depends on data type* Depends on data type* Depends on data type* Depends on data type*
combobox No Yes Yes Yes Yes No
currency** Yes Yes Yes Yes Yes Yes
DataCategoryGroupReference No Yes Yes Yes Yes No
email No Yes Yes Yes Yes No
encryptedstring No No No No No No
location No No No No No No
ID No Yes Yes Yes Yes No
masterrecord No Yes Yes Yes Yes No
multipicklist No No No No No No
percent Yes Yes Yes Yes Yes Yes
phone No Yes Yes Yes Yes No
picklist No Yes Yes Yes Yes No
reference No Yes Yes Yes Yes No
textarea No Yes Yes Yes Yes No
url No Yes Yes Yes Yes No

Salesforce Apex Trigger Framework

Benefits of Apex Trigger Framework:

  • A single trigger per object gives complete control over the order of execution.
  • Implementing trigger logic in handler class, makes unit testing and maintenance much easier.
  • Implementation of best practices.
  • It enforces trigger to work in a consistent way.
  • It allows to prevent trigger recursion without adding separate logic.
  • It is easier to work on a single trigger for multiple developers and reduce the development lifecycle.
  • It allows to make decisions to active/inactive the trigger from transaction and UI as well.

Each trigger must be implemented in a custom setting that allows the trigger to be active/inactive from UI.
Custom Settings: (Trigger Setting)

Trigger Setting Data: (Create record for each trigger)

Trigger Interface: (ITriggerHandler)

/*
@Author : Biswajeet Samal
@CreatedDate : 20th Oct 2019
@Description : Trigger Handler Interface
*/
public interface ITriggerHandler{
    
    /*
@Author : Biswajeet Samal
@CreatedDate : 20th Oct 2019
@Description : Called by the trigger framework before insert of the records
@Parameters : List<sObject> newList , Map<Id, sObject> newMap
*/
    void beforeInsert(List<sObject> newList);
    
    /*
@Author : Biswajeet Samal
@CreatedDate : 20th Oct 2019
@Description : Called by the trigger framework after insert of the records
@Parameters : List<sObject> newList
*/
    void afterInsert(List<sObject> newList, Map<Id, sObject> newMap);
    
    /*
@Author : Biswajeet Samal
@CreatedDate : 20th Oct 2019
@Description : Called by the trigger framework before update of the records
@Parameters : List<sObject> newList, Map<Id, sObject> newMap,  List<sObject> oldList, Map<Id, sObject> oldMap
*/
    void beforeUpdate(List<sObject> newList, Map<Id, sObject> newMap,  List<sObject> oldList, Map<Id, sObject> oldMap);
    
    /*
@Author : Biswajeet Samal
@CreatedDate : 20th Oct 2019
@Description : Called by the trigger framework after update of the records
@Parameters : List<sObject> newList, Map<Id, sObject> newMap,  List<sObject> oldList, Map<Id, sObject> oldMap
*/
    void afterUpdate(List<sObject> newList, Map<Id, sObject> newMap,  List<sObject> oldList, Map<Id, sObject> oldMap);
    
    /*
@Author : Biswajeet Samal
@CreatedDate : 20th Oct 2019
@Description : Called by the trigger framework before delete of the records
@Parameters : List<sObject> oldList , Map<Id, sObject> oldMap
*/            
    void beforeDelete(List<sObject> oldList , Map<Id, sObject> oldMap);
    
    /*
@Author : Biswajeet Samal
@CreatedDate : 20th Oct 2019
@Description : Called by the trigger framework after delete of the records
@Parameters : Map<Id, sObject> oldMap
*/
    void afterDelete(List<sObject> oldList , Map<Id, sObject> oldMap);
    
    /*
@Author : Biswajeet Samal
@CreatedDate : 20th Oct 2019
@Description : Called by the trigger framework after undelete of the records
@Parameters : List<sObject> newList, Map<Id, sObject> newMap
*/
    void afterUnDelete(List<sObject> newList, Map<Id, sObject> newMap);
    
    /*
@Author : Biswajeet Samal
@CreatedDate : 20th Oct 2019
@Description : Called by the trigger framework to check the trigger for the object is enabled or disabled
@Parameters :
*/
    Boolean isDisabled();
}

Trigger Dispatcher Class:(TriggerDispatcher)

/*
@Author : Biswajeet Samal
@CreatedDate : 20th Oct 2019
@Description : Trigger Dispatcher.
*/
public class TriggerDispatcher {
    
    /*
@Author : Biswajeet Samal
@CreatedDate : 20th Oct 2019
@Description : It will invoke the appropriate methods on the handler depending on the trigger context.
@Parameters : ITriggerHandler handler
*/
    public static void run(ITriggerHandler handler, string triggerName){
        
        //Check if the trigger is disabled
        if (handler.IsDisabled()){
            return;
        }
        
        //Get the trigger active information from custom settings by trigger name
        Boolean isActive = TriggerSetting__c.getValues(triggerName).isActive__c;
        
        if(isActive){
            //Check trigger context from trigger operation type
            switch on Trigger.operationType {
                
                when BEFORE_INSERT {
                    //Invoke before insert trigger handler
                    handler.beforeInsert(trigger.new);
                }
                when AFTER_INSERT {
                    //Invoke after insert trigger handler
                    handler.afterInsert(trigger.new, trigger.newMap);
                }
                when BEFORE_UPDATE {
                    //Invoke before update trigger handler
                    handler.beforeUpdate(trigger.new, trigger.newMap, trigger.old, trigger.oldMap);
                }
                when AFTER_UPDATE {
                    //Invoke after update trigger handler
                    handler.afterUpdate(trigger.new, trigger.newMap, trigger.old, trigger.oldMap);
                }
                when BEFORE_DELETE {
                    //Invoke before delete trigger handler
                    handler.beforeDelete(trigger.old, trigger.oldMap);
                }
                when AFTER_DELETE {
                    //Invoke after delete trigger handler
                    handler.afterDelete(trigger.old, trigger.oldMap);
                }
                when AFTER_UNDELETE {
                    //Invoke after undelete trigger handler
                    handler.afterUnDelete(trigger.new, trigger.newMap);
                }
            }
        }
    }
}

Account Object Trigger: (AccountTrigger)

/*
@Author : Biswajeet Samal
@CreatedDate : 20th Oct 2019
@Description : Account Object Trigger.
*/
trigger AccountTrigger on Account(before insert, after insert, before update, after update, before delete, after delete, after unDelete) {
    TriggerDispatcher.run(new AccountTriggerHandler(), 'AccountTrigger');
}

Account Object Trigger Handler: (AccountTriggerHandler)

/*
@Author : Biswajeet Samal
@CreatedDate : 20th Oct 2019
@Description : Account Object Trigger Handler.
*/
public class AccountTriggerHandler implements ITriggerHandler{
    
    //Use this variable to disable this trigger from transaction
    public static Boolean TriggerDisabled = false;
    
    //check if the trigger is disabled from transaction
    public Boolean isDisabled(){
        return TriggerDisabled;
    }
    
    public void beforeInsert(List<sObject> newList) {
        
    }
    
    public void afterInsert(List<sObject> newList , Map<Id, sObject> newMap) {
        
    }
    
    public void beforeUpdate(List<sObject> newList, Map<Id, sObject> newMap, List<sObject> oldList, Map<Id, sObject> oldMap) {
        
    }
    
    public void afterUpdate(List<sObject> newList, Map<Id, sObject> newMap,  List<sObject> oldList, Map<Id, sObject> oldMap) {
        
    }
    
    public void beforeDelete(List<sObject> oldList , Map<Id, sObject> oldMap) {
        
    }
    
    public void afterDelete(List<sObject> oldList , Map<Id, sObject> oldMap) {
        
    }
    
    public void afterUnDelete(List<sObject> newList, Map<Id, sObject> newMap) {
        
    }
}

Count Unique Row Values in Salesforce Lightning Report

In Winter ’20 release Salesforce has introduced report Count Unique Row Values in Lightning experience.

Go to Setup | Reports and Dashboards Settings | Select Enable Unique Row Count Aggregate in Reports (Lightning Experience Only) | click Save.

While editing a report in the report builder, find the column for which you want to count unique values | Click Show More | Show Unique Count | Click Save.