Tag Archives: SOQL

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

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];  

Using OFFSET in SOQL

When expecting many records in a query’s results, we can display the results in multiple pages by using the OFFSET clause in a SOQL query. We can use OFFSET keyword in SOQL to specify the starting row from the result returned by the query. For example if there are 50 records then, if we specify offset as 20 in the query then it would return record 21 to 50, it will skip first 20 records.

In the following example the query will return rows 11 through 110, and will skip first 10 rows.

SELECT Name, AccountNumber, Type
FROM Account
WHERE Type = 'Customer - Direct'
ORDER BY Name
LIMIT 100
OFFSET 10

Considerations to Use OFFSET:

  • The maximum offset is 2,000 rows. Requesting an offset greater than 2,000 results in a NUMBER_OUTSIDE_VALID_RANGE error.
  • A sub-query can use OFFSET only if the parent query has a LIMIT 1 clause.
  • OFFSET cannot be used as a sub-query in the WHERE clause, even if the parent query uses LIMIT 1.
  • Salesforce recommend using an ORDER BY clause when you use OFFSET to ensure that the result set ordering is consistent. The row order of a result set that does not have an ORDER BY clause has a stable ordering, however the ordering key is subject to change and should not be relied on.
  • Also Salesforce recommend using a LIMIT clause with OFFSET if you need to retrieve subsequent subsets of the same result set.
  • OFFSET clause can be used to implement pagination in visualforce page tables.
  • OFFSET is applied to the result set returned at the time of the query. No server-side cursor is created to cache the full result set for future OFFSET queries. The page results may change if the underlying data is modified during multiple queries using OFFSET into the same result set.
  • When using OFFSET, only the first batch of records are returned for a given query. If you want to retrieve the next batch, you’ll need to re-execute the query with a higher offset value.
  • The OFFSET clause is allowed in SOQL used in SOAP API, REST API, and Apex. It’s not allowed in SOQL used within Bulk API or Streaming API.

SOQL Query for Tasks, Notes and Events by Object Type

Tasks:

//The below query returns a list of Tasks related to Leads
SELECT Id, Who.Id, Who.Type FROM Task WHERE Who.Type = 'Lead'

Notes:

//The below query returns a list of Notes where the parent is a specific type, in this case Opportunity
SELECT Id, Parent.Id, Parent.Type FROM Note WHERE Parent.Type = 'Opportunity'

Events:

//The below query returns a list of Events related to Account and Opportunity
SELECT Id, Subject, What.Type, whatId FROM Event WHERE What.Type IN ('Account', 'Opportunity')