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!
Frequently Asked Questions (FAQs)
1. What is SOQL?
SOQL (Salesforce Object Query Language) is used to query data in Salesforce.
2. What are aggregate functions in SOQL?
Aggregate functions like COUNT(), MIN(), MAX(), SUM(), and AVG() perform calculations on your data.
3. How can I filter records in SOQL?
Use conditional expressions such as =, !=, <, >, LIKE, IN, and NOT IN to filter records.
4. What are date literals in SOQL?
Date literals like TODAY, YESTERDAY, THIS_WEEK, and THIS_YEAR simplify date-based queries.
5. Can I use multiple conditions in one SOQL query?
Yes, you can combine multiple conditions using AND/OR operators.