GROUP BY Date Clause in SOQL (Salesforce Object Query Language)

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.

Table of Contents

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 field. The CALENDAR_YEAR function is used to extract the year component of a date field, enabling you to group records by year.

For example, you want to know the year the record was created and how many account records are created annually, and you want to display the record count for each year.

SELECT CALENDAR_YEAR (CreatedDate), COUNT(Id) FROM Account GROUP BY CALENDAR_YEAR(CreatedDate)

The above SOQL query added a CreatedDate field. We want to know the year the record was created, so we used the CALENDAR_YEAR() function to get the year. COUNT() is used to get the number of records.

Then, using the GROUP BY clause, we display the year, and the record counts the records created for each year.

In this way, we can use GROUP BY YEAR to get the year the account record was created and the count of those records by each year using a SOQL query.

Example: GROUP BY Month in SOQL

In SOQL, the CALENDAR_MONTH() function combined with the GROUP BY function can group records by the month of a specific date field.

For example, you want to retrieve the number of records grouped by month when the opportunity records are closed.

SELECT CALENDAR_MONTH(CloseDate), COUNT(Id) FROM Opportunity GROUP BY CALENDAR_MONTH(CloseDate)

In the above SOQL query, using CALENDAR_MONTH(), we obtained the month of the opportunity close date and the record count for the number of records closed in each month. CALENDAR_MONTH() returns an integer from 1 to 12, depending on the month of the year.

In this way, we can use the GROUP BY DAY clause to get the number of opportunity records for each month and the time the opportunity was closed.

Example: GROUP BY Day of the Week in SOQL

The group-by-day function retrieves the day of the week for a given date field in a query. For that, we have the DAY_IN_WEEK() function. Now, let’s take an example: You want to fetch data grouped by days and also fetch the number of records created on that particular day.

SELECT DAY_IN_WEEK(CreatedDate), COUNT(Id) FROM Case GROUP BY DAY_IN_WEEK(CreatedDate)

In the above SOQL query, we used DAY_IN_WEEK() to get the day of the week, and by grouping the days, we fetched the number of records created from each grouping day.

In the result, DAY_IN_WEEK() 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.

In this way, we can use the GROUP BY WEEKDAY clause to get the number of case records for each day and the time the cases were created.

Example: GROUP BY Last Modified Date in SOQL

In SOQL, grouping directly by LastModifiedDate without date functions will group records with the same date and time.

This is typically not very useful because timestamps are displayed in detail. To group by a particular date, we can use the CALENDAR_YEAR and CALENDAR_MONTH in combination with the GROUP BY clause.

For example, we want to display the account records grouped by the last modified date, month, and year. With that, we also want to display the record count in each group using a SOQL query.

SELECT COUNT(Id), CALENDAR_YEAR(LastModifiedDate), CALENDAR_MONTH(LastModifiedDate) FROM Account GROUP BY CALENDAR_YEAR(LastModifiedDate), CALENDAR_MONTH(LastModifiedDate)

In the above SOQL query, we fetched the account records in the grouping of the years and months when they were last modified. CALENDAR_MONTH() returns the year of the last modified date, and CALENDAR_MONTH() returns the month of the last modified date.

Example: Sort Records Using GROUP BY Month in SOQL

After retrieving the data, sort the query result by grouping the records monthly using the CALENDAR_MONTH() function so that each record is displayed in a group by the date field. To sort the query result, we need to use the ORDER BY clause in SOQL.

For example, when we fetch data from the Salesforce database using a query without sorting, it is displayed randomly or in the order of the last modified date. However, we now want to display the query result in a specific order, so we need to use the ORDER BY clause.

SELECT CALENDAR_MONTH(CreatedDate) Month, COUNT(Id) FROM Contact GROUP BY CALENDAR_MONTH(CreatedDate) ORDER BY CALENDAR_MONTH(CreatedDate)

Using the above SOQL query, we fetched the contact records in the grouping of the months when they were created. CALENDAR_MONTH() returns an integer from 1 to 12, depending on the month of the year.

After that, we used the ORDER BY clause. We sorted the groups of months in ascending order or controlled the order of the results.

Sort Records Using GROUP BY Month in SOQL

In this way, we can use the GROUP BY MONTH and ORDER BY clauses to group the months, get the number of records for each month when they are created and sort the result order.

Conclusion

I hope you have got an idea about the GROUP BY date clause in SOQL (Salesforce Object Query Language). I have explained 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.

DOWNLOAD FREE AGENTFORCE EBOOK

FREE SALESFORCE FLOW EBOOK