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