Tag Archives: Aggregate Functions

SOQL Group By Clause

Biswajeet   December 9, 2019   No Comments on 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

Using AggregateResult in Salesforce

Biswajeet   February 25, 2014   No Comments on Using AggregateResult in Salesforce

The aggregate functions COUNT(fieldname), COUNT_DISTINCT(), SUM(), AVG(), MIN() and MAX() in SOQL return an AggregateResult object or a List of AggregateResult objects. We can use aggregate functions result in apex by using AggregateResult object.

Here is an example to use AggregateResult in Salesforce. In below example I’m using COUNT(fieldname) aggregate function in SOQL to show Account record respective number of Contacts.

Visualforce Page:

<apex:page controller="SampleController" action="{!getData}">
    <apex:form >
        <apex:pageBlock >
            <apex:pageblockTable value="{!accWrapList}" var="acc">            
                <apex:column headerValue="Account Name" value="{!acc.AccountName}"/>
                <apex:column headerValue="Number of Contacts" value="{!acc.TotalContact}"/>
            </apex:pageblockTable>
        </apex:pageBlock>
    </apex:form>
</apex:page>

Apex Class:

public with sharing class SampleController {
    public List<AggregateResult> result {get;set;}    
    public List<AccWrapper> accWrapList {get;set;}
    public List<Account> accList;
    public Map<Id, Account> accMap;
    List<Id> idList;
    
    public void getData() {
        accWrapList = new List<AccWrapper>();
        result = new List<AggregateResult>();
        idList = new List<Id>();
        accList = new List<Account>();
        accMap = new Map<Id, Account>();
        
        result = [SELECT COUNT(Id) Total, AccountId FROM Contact WHERE AccountId != null GROUP BY AccountId];               
        
        for(AggregateResult a : Result) {   
            idList.add((Id)a.get('AccountId'));
        }
        
        accList = [SELECT Id, Name FROM Account WHERE Id IN : idList];
        for(Account a : accList) {
            accMap.put(a.Id, a);            
        }
        
        for(AggregateResult aResult : result) {
            Account acc = accMap.get((Id)(aResult.get('AccountId')));
            accWrapList.add(new AccWrapper(aResult, acc.Name)); 
        }
    }
    
    public class AccWrapper {
        public Integer TotalContact {get;set;}
        public String AccountName {get;set;}
        
        public AccWrapper(AggregateResult a, String AccountName) {
            this.TotalContact =  (Integer)a.get('Total');
            this.AccountName = AccountName;
        }
    }
}

Output:

Aggregate Functions in SOQL

Biswajeet   February 25, 2014   No Comments on Aggregate Functions in SOQL

In database management an aggregate function is a function where the values of multiple rows are grouped together to form a single value. In Salesforce SOQL aggregate functions are same as SQL aggregate function.

The following aggregate functions are provided by SOQL:

  • AVG()
  • COUNT() and COUNT(fieldName)
  • COUNT_DISTINCT()
  • MIN()
  • MAX()
  • SUM()

AVG(): Returns the average value of a numeric field.

Example:

SELECT CampaignId, AVG(Amount)
FROM Opportunity
GROUP BY CampaignId

COUNT() and COUNT(fieldName): Returns the number of rows matching the query criteria. COUNT(Id) in SOQL is equivalent to COUNT(*) in SQL. COUNT(fieldName) available in API version 18.0 and later. If you are using a GROUP BY clause, use COUNT(fieldName) instead of COUNT().

Example using COUNT():

SELECT COUNT()
FROM Account
WHERE Name LIKE 'a%'

Example using COUNT(fieldName):

SELECT COUNT(Id)
FROM Account
WHERE Name LIKE 'a%'

COUNT_DISTINCT(): Returns the number of distinct non-null field values matching the query criteria. COUNT_DISTINCT(fieldName) in SOQL is equivalent to COUNT(DISTINCT fieldName) in SQL. To query for all the distinct values, including null, for an object, see GROUP BY. Available in API version 18.0 and later.

Example:

SELECT COUNT_DISTINCT(Company)
FROM Lead

MIN(): Returns the minimum value of a field. If you use the MIN() or MAX() functions on a picklist field, the function uses the sort order of the picklist values instead of alphabetical order. Available in API version 18.0 and later.

Example:

SELECT MIN(CreatedDate), FirstName, LastName
FROM Contact
GROUP BY FirstName, LastName

MAX(): Returns the maximum value of a field.
Example:

SELECT Name, MAX(BudgetedCost)
FROM Campaign
GROUP BY Name

SUM(): Returns the total sum of a numeric field.
Example:

SELECT SUM(Amount)
FROM Opportunity
WHERE IsClosed = false AND Probability > 60

Note: You can’t use a LIMIT clause in a query that uses an aggregate function. The following query is invalid:

SELECT MAX(CreatedDate)
FROM Account LIMIT 1

The aggregate functions COUNT(fieldname), COUNT_DISTINCT(), SUM(), AVG(), MIN() and MAX() in SOQL return an AggregateResult object or a List of AggregateResult objects. You can use aggregate functions result in apex by using AggregateResult object.

Example:

List<AggregateResult> result = [SELECT COUNT(Id) Total, AccountId FROM Contact WHERE AccountId != null GROUP BY AccountId];