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 ()
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.
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.
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.
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: