SOQL Functions with Examples

SOQL helps developers retrieve data from Salesforce objects in a powerful and structured way. One of the most important features in SOQL is its support for functions which help you manipulate, analyze and summarize your data. In this post we will breakdown the most commonly used SOQL functions with examples, so you can query Salesforce data more effectively.

Aggregate Functions

Aggregate functions in SOQL allow you to perform calculations on sets of data like performing totals, averages, or counts without retrieving individual records. They’re useful for reports, dashboards or optimizing logic in Apex.

While aggregate functions help with summarizing data, sometimes you need to traverse related records to pull meaningful insights for that checkout our detailed post on SOQL Relationship queries

COUNT()

The COUNT() function returns the number of records that match your query criteria. It counts only non-null values of specified field.

SELECT COUNT(Id) 
FROM Account 
WHERE Industry = 'Energy'

AVG()

The AVG() function returns the average of a numeric field.

SELECT AVG(AnnualRevenue) 
FROM Account WHERE Name LIKE '%&%'

Above query will return average of annual revenue of records which have “&” in their name.

SUM()

Returns the total value of a numeric field.

SELECT SUM(AnnualRevenue) 
FROM Account

Above query will return summation of annual revenue of all accounts present in org.

MIN() and MAX()

Use these to find the lowest and highest value in a field

SELECT MIN(CreatedDate), MAX(CreatedDate) 
FROM Account

Above query will return earliest and latest created accounts among all accounts.

GROUP BY Clause

The GROUP BY clause is used to group records by a specific field when using aggregate functions. This lets you summarize data as per category.

SELECT COUNT(Id), Industry 
FROM Account 
GROUP BY Industry

Above query will return the number of accounts present for each Industry type.

HAVING Clause

HAVING clause is used to filter aggregated results – similar to how WHERE filters individual records. You can only use HAVING with GROUP BY queries.

SELECT COUNT(Id), Industry 
FROM Account 
GROUP BY Industry HAVING COUNT(Id) > 1

This query returns only those industries that have more than 1 account.

Date functions

CALENDAR_YEAR()

This date function allows you to group, or filter based on calendar year

SELECT CALENDAR_YEAR(CreatedDate), SUM(AnnualRevenue) 
FROM Account 
GROUP BY CALENDAR_YEAR(CreatedDate)

This query will return sum of annual revenue of all accounts created in each year.

CALENDAR_MONTH()

This function returns a number representing the calendar month of a date field. 1 for January and 12 for December.

SELECT CALENDAR_MONTH(CreatedDate), COUNT(Id) 
FROM Case 
WHERE CreatedDate = LAST_N_MONTHS:12 
GROUP BY CALENDAR_MONTH(CreatedDate)

Above query will return case count per month over last 12 months.

There are various other date functions too for example, CALENDAR_QUARTER(), DAY_IN_MONTH(), DAY_IN_WEEK(), FISCAL_YEAR(), FISCAL_MONTH() etc.

FORMAT()

FORMAT() returns localized formatted value of number, date, time and currency field. Formatting will depend upon user locale.

FORMAT function on DateTime field

SELECT Id, Name, FORMAT(CreatedDate) 
FROM Account 
ORDER BY CreatedDate DESC LIMIT 2

FORMAT function on Currency field

SELECT Id, FORMAT(Amount), Name 
FROM Opportunity

toLabel()

This function will return readable label values of picklist values and record types. Sometimes API Name and label of picklist values are different if we want to handle code logic according to label of picklist value then we should use toLabel in SOQL.

SELECT Id, Name, StageName 
FROM Opportunity

Mastering SOQL functions with examples helps you

• Improve query performance
• Analyze data directly within SOQL query
• Write cleaner Apex.

Whether you are writing complex apex logic or building reports and dashboards knowing how to use SOQL functions with examples is essential for working efficiently with salesforce data.