When you want to track customer support cases and determine the number of cases created each day for the past month to identify the top activity periods, the group records data by time periods, such as days, months, or years, and performs aggregate calculations.
In this Salesforce tutorial, we will learn about the GROUP BY date clause in SOQL (Salesforce Object Query Language). I will explain the functionality of the GROUP BY clause and how to use the date functions with the GROUP BY clause in SOQL queries with different examples.
GROUP BY Date Clause in SOQL
The GROUP BY clause in SOQL is used to combine data based on one or more fields. When used with a date or date/time field, it enables us to group records by a specific part of the date, such as the year, month, or day. This is useful for reporting and summarizing data over time.
When grouping by a date field, Salesforce automatically groups records based on the full date value (including time, if the field is a datetime type). To group by specific parts of the date, you must use date functions like CALENDAR_YEAR, CALENDAR_MONTH, or DAY_IN_MONTH.
Syntax:
SELECT <fields>, COUNT(Id) FROM <object> GROUP BY <date_field>Supported Functions for Date Grouping:
1. CALENDAR_YEAR(): This function in SOQL filters or groups records based on the year part. It extracts the year from the standard or custom date field in the query. This function is handy when you want to query records from a specific year or aggregate data for a particular year.
The CALENDAR_MONTH() function is similar to the YEAR() function, but instead of returning a year, it returns the month of the date field.
2. CALENDAR_QUARTER(): This function returns the calendar quarter of a given date or date/time field in the SOQL query. Below, I have provided the calendar quarter for the year.
- Quarter 1 (Q1): January 1 – March 31
- Quarter 2 (Q2): April 1 – June 30
- Quarter 3 (Q3): July 1 – September 30
- Quarter 4 (Q4): October 1 – December 31
This function returns an integer representing the quarter. For example, if records are from the first quarter, it returns 1 value.
3. WEEK_IN_YEAR(): This function returns the week of the year for a given date. The week is represented as an integer value, starting from 1 for the first week of the year to 52 or 53, depending on the year and how weeks align with calendar dates.
When we use the WEEK_IN_YEAR() function in the SOQL query, we cannot use the ORDER BY clause.
4. DAY_IN_MONTH(): This SOQL function extracts the day of the month from a date field in a Salesforce object. It returns an integer value representing the day part of a given date. An integer from 1 to 31, depending on the day of the month.
5. DAY_IN_WEEK(): This function in Salesforce Object Query Language (SOQL) retrieves the day of the week for a given date field in a query. It returns an integer between 1 and 7, which displays a specific day of the week, starting with Sunday as 1 and ending with Saturday as 7.
Example: GROUP BY Year in SOQL
In SOQL, you can use the GROUP BY year clause to aggregate data based on specific criteria, such as grouping by the year of a date