SOQL Cheatsheet: Aggregate Functions, Conditional Expressions, Date Literals

Updated:

July 13, 2024

⏱️ 3 minutes

SOQL (Salesforce Object Query Language) is a powerful tool for querying data in Salesforce. Whether you’re a developer, administrator, or Salesforce enthusiast, understanding SOQL’s aggregate functions, conditional expressions, and date literals can significantly enhance your data manipulation and reporting capabilities.

This guide provides a handy cheat sheet and explanations to help you get the most out of your SOQL queries.

Aggregate Functions in SOQL

Aggregate functions allow you to perform calculations on your data directly within your SOQL queries. Here are the key aggregate functions you need to know:

Command	Description	Example
COUNT()	Count the number of records	SELECT COUNT() FROM Account
MIN()	Returns the minimum value of a field	SELECT MIN(Amount) FROM Opportunity
MAX()	To get the maximum value of a field	SELECT MAX(Amount) FROM Opportunity
SUM()	Returns the total sum of a numeric field	SELECT SUM(Amount) FROM Opportunity
AVG()	It will return the average value of a field	SELECT AVG(Amount) FROM Opportunity

Conditional Expressions in SOQL

Conditional expressions are used to filter records based on specific criteria. Here’s a breakdown of the main conditional expressions:

Command	Description	Example
=	Equals	SELECT FirstName, LastName FROM Contact WHERE LastName = ‘Smith’
!=	Not Equals	SELECT Name, StageName FROM Opportunity WHERE StageName != ‘Closed Won’
<, <=	Less Than, Less Than or Equal To	SELECT CaseNumber, CreatedDate FROM Case WHERE CreatedDate < 2023-01-01
>, >=	Greater Than, Greater Than or Equal To	SELECT Name, LeadScore FROM Lead WHERE LeadScore > 50
INCLUDES, EXCLUDES	Includes or Excludes Values (Multi-Select Picklists)	SELECT Id, CustomField__c FROM CustomObject__c WHERE CustomField__c INCLUDES (‘Option1’)
LIKE	Returns records matching a pattern	SELECT Name FROM Account WHERE Name LIKE ‘%Inc’
IN	Field matches any specified values	SELECT Name, Type FROM Account WHERE Type IN (‘Customer’, ‘Partner’)
NOT IN	Field does not match specified values	SELECT Name, Email FROM Contact WHERE Email NOT IN (‘%@gmail.com’, ‘%@yahoo.com’)

Date Literals in SOQL

Date literals simplify working with dates in your SOQL queries. Here’s a list of commonly used date literals:

Command	Description	Example
TODAY	Starts at 12:00:00 AM and continues for 24 hours	SELECT Id FROM Account WHERE CreatedDate > TODAY
YESTERDAY	Day before the current day	SELECT Id FROM Account WHERE CreatedDate = YESTERDAY
TOMORROW	Day after the current day	SELECT Id FROM Account WHERE CreatedDate = TOMORROW
THIS_WEEK	First day of the current week and continues for seven days	SELECT Id FROM Account WHERE CreatedDate = THIS_WEEK
LAST_90_DAYS	90 days before the current day	SELECT Id FROM Account WHERE CreatedDate = LAST_90_DAYS
LAST_MONTH	The first day of the previous month and continues for all the days of that month	SELECT Id FROM Account WHERE CreatedDate = LAST_MONTH
THIS_YEAR	January 1 of the current year through December 31 of the current year	SELECT Id FROM Account WHERE CreatedDate = THIS_YEAR

Also Read – What is Time and DateTime in Salesforce’s APEX

Conclusion

Mastering SOQL requires a good understanding of its functions, expressions, and literals. This cheat sheet is a quick reference to help you write efficient and effective queries. By leveraging aggregate functions, conditional expressions, and date literals, you can perform complex data manipulations and gain valuable insights from your Salesforce data.

Start your Salesforce journey today with saasguru! With a free trial that gives you access to over 30 Salesforce Certification Courses, 50+ Mock Exams, and 50+ Salesforce Labs for practical learning, you’re set for success.

Begin your training with saasguru and enhance your professional capabilities!