SOQL Functions Every Salesforce Developer Should Know

Salesforce Object Query Language (SOQL) is a powerful tool for retrieving data from your Salesforce org. While similar to SQL, SOQL is optimized for Salesforce’s data model, offering specialized functions to streamline queries. This guide explores key SOQL functions, their syntax, and practical use cases to enhance your data retrieval strategies. Join us to learn about SOQL Functions Every Salesforce Developer Should Know.

SOQL Functions Every Salesforce Developer Should Know

Let’s break down all supported functions and methods in detail, with examples. Check SOQL best practices to learn more.

1. Aggregate Functions

Aggregate functions are used to perform calculations on groups of records, often combined with GROUP BY clauses. They are essential for summarizing data in reports, dashboards, or Apex logic.

COUNT()

Purpose: Counts the number of records in a query or grouped set.

Use Case: Track the number of open opportunities by stage.

Example:

SELECT StageName, COUNT(Id) OpportunityCount  

FROM Opportunity  

WHERE IsClosed = false  

GROUP BY StageName

In Salesforce SOQL, you can use HAVING with GROUP BY and COUNT() for better results.

SELECT StageName, COUNT(id) OpportunityCount FROM Opportunity WHERE IsClosed = false GROUP BY StageName having COUNT(id) > 5

This SOQL query fetches open opportunities (IsClosed = false), groups them by StageName, and returns only those stages with more than 5 opportunities. It uses COUNT(Id) to count records per stage and filters the grouped results using the HAVING clause.

SUM()

Purpose: Calculates the total value of a numeric field.

Use Case: Calculate the total revenue generated by each account.

Example:

SELECT  SUM(Amount) TotalRevenue FROM Opportunity WHERE StageName = 'Closed Won' GROUP BY Account.Name having SUM(Amount)  > 0

AVG()

Purpose: Computes the average value of a numeric field.

Use Case: Determine the average deal size for opportunities closed this quarter.

Example:

SELECT AVG(Amount) AverageDealSize FROM Opportunity WHERE CloseDate = THIS_QUARTER

MAX() / MIN()

Purpose: Identifies the highest or lowest value in a field.

Use Case: Find the earliest and latest case resolution times.

Example:

SELECT MIN(ClosedDate), MAX(ClosedDate) FROM Case WHERE Status = 'Closed'

2. Date Functions

Date functions manipulate and format date/datetime fields, making them invaluable for time-based analysis.

These extract components of a date and can be used in SELECT, WHERE, ORDER BY, and GROUP BY. 

• CALENDAR_YEAR(DateField)
• CALENDAR_MONTH(DateField)
• CALENDAR_QUARTER(DateField)
• DAY_IN_MONTH(DateField)
• DAY_ONLY(DateTimeField)
• FISCAL_YEAR(DateField)
• FISCAL_QUARTER(DateField)

Examples:

— Used in SELECT

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

— Used in WHERE

SELECT Id FROM Opportunity WHERE CALENDAR_MONTH(CloseDate) = 5

— Used in ORDER BY

SELECT Name FROM Opportunity ORDER BY CALENDAR_YEAR(CloseDate)

These functions help you dynamically query and analyze Salesforce data across different time frames with ease.

3. Formatting Functions in SOQL

Formatting functions in SOQL allow you to present fields in a more user-friendly or localized format. These are especially helpful in reports, dashboards, and UI-driven queries.

FORMAT(field)

• Converts fields like currency, percent, date, and number into formatted strings.
• Useful when showing values as users see them in the Salesforce UI.
• Only allowed in SELECT clause.

Example: 

SELECT FORMAT(CloseDate), FORMAT(Amount) FROM Opportunity where Amount <> null limit 5

Without using Format() method: 

SELECT CloseDate, Amount FROM Opportunity where Amount <> null limit 5

toLabel():

Translate SOQL query results into the language of the user who submits the query using the toLabel function. If translation workbench is enabled, you can used this function to convert result into user’s language

Example: 

SELECT Company, Status, toLabel(Status) translatedStatus FROM Lead

You can use the toLabel() method to filter records using a translated picklist value. For example:

SELECT Company, toLabel(Status) FROM Lead WHERE toLabel(Status) = 'le Draft'

convertTimezone(): 

You can use convertTimezone() in a date function to convert dateTime fields to the user’s time zone.

For example, you could use the convertTimezone(dateTimeField) function to find the sum of the Amount values for all your opportunities for each hour of the day, where the hour is converted to the user’s time zone.

SELECT HOUR_IN_DAY(convertTimezone(CreatedDate)), SUM(Amount) FROM Opportunity GROUP BY HOUR_IN_DAY(convertTimezone(CreatedDate)) 

Note that you can only use convertTimezone() in a date function. The following query doesn’t work because there is no date function.

SELECT convertTimezone(CreatedDate) FROM Opportunity 

4. Geolocation Functions

Geolocation functions calculate distances between locations, perfect for field service or location-based analytics.

DISTANCE()

DISTANCE(mylocation1, mylocation2, ‘unit’) and replace mylocation1 and mylocation2 with two location fields, or a location field and a value returned by the GEOLOCATION function. Replace unit with mi (miles) or km (kilometers). 

GEOLOCATION()

This method Returns a geolocation based on the provided latitude and longitude. Must be used with the DISTANCE function.

Usage: GEOLOCATION(latitude, longitude) and replace latitude and longitude with the corresponding geolocation, numerical code values. 

Compare two field values, or a field value with a fixed location. For example:

SELECT Name, Account_Location__c FROM Account  WHERE DISTANCE(Account_Location__c, GEOLOCATION(37.775,-122.418), 'mi') > 20

Conclusion

SOQL functions transform raw data into actionable insights, whether you’re analyzing sales trends, formatting dates for global teams, or optimizing field service routes. By knowing these functions, you’ll unlock the full potential of Salesforce data retrievals. Check SOQL Cheat sheet to earn about other operatthe SOQL Cheat sheet to learn about other operatooers.