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:
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;
}
}
}
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];
.png)
0 Comments