SOQL.

DEV

FREE Basic Reference eBook to Salesforce.com Object Query Language.

Chapter 1

WHAT IS SOQL (SALESFORCE.COM

OBJECT QUERY LANGUAGE)?

Prerequisites: Basic SQL Knowledge.

SOQL to build your own custom query strings. These query strings can be used in
the following places:
1. Apex statements
2. Visualforce getter methods and controllers
3. In the queryString param passed in the query() call
4. Finally, you can use the Schema Explorer in the Eclipse Toolkit
(this one is pretty slick)
For those of you familiar with SQL, you will find some differences but for
the most part, SOQL does just about everything you'll need it to do.
SOQL uses the "SELECT" statement combined with any filter statements to
bring back sets of data. The data sets returned may be optionally ordered as well
(just like in SQL). Here is a basic example:
SELECT field1, field2, field3
FROM an object
WHERE filter statement(s) and (optionally) order the results
So, if we want to get all the Leads from your Salesforce.com account where
the email address equals = "john.doe@somecompany.com" you would use the
following SOQL statement:
SELECT ID, Name from Lead WHERE email =
'Mark.White@somecompany.com'
For detailed documentation on the syntax of a SOQL SELECT statement, go to
this Salesforce.com developer documentation page

Chapter 2

COUNT()

Getting the "Count" of results being returned in a SOQL data set is pretty simple as
well.

For example, if you wanted to know how many leads were going to be returned in
my SELECT statement above, I can use the COUNT() function below:
SELECT COUNT() from Lead WHERE email = 'Mark.White@YourCompanyDomainName.com'

Chapter 3

COMPARISON OPERATORS

Operator Common name
= Equals
!= Not Equals
< Less than
<= Less than or equal
> Greater than
>= Greater than or equal
IN In
NOT IN Not in (WHERE clause)
INCLUDES
Applies to multi-select pick
EXCLUDES
LIKE Like
For a full chart of all the comparison operators, check out this
Salesforce.com developer documentation page.
Chapter 4

LIKE OPERATOR

The LIKE operator provides a way to match partial text strings and includes
support for wildcards. Let's say for a moment we want to find all the Leads where
the email domain is the same. For this, we can use a "LIKE" operator. He is an
example of a LIKE statement with the % wildcard.
SELECT Id, Name from Lead WHERE email LIKE '%somecompany.com'
The placement of the per cent sign '%' is key here. I am basically saying,
bring me back all the Leads where the email ends with "somecompany.com".
Therefore I place the '%' at the beginning of whatever I am looking for." Anything
to the left of the % sign is ignored in the search. If I didn't know the full domain I
could use the following statement:
SELECT Id, Name from Lead WHERE email LIKE '%somecomp%'
This is going to return all the leads where the email contains "somecomp".
Another wildcard is the underscore "_". Thing is used to match exactly one
character.
Note: Unlike with SQL, the LIKE operator in SOQL performs a case-
insensitive match.


Chapter 5

WHERE/OR

If you want to extend the WHERE clause to include multiple values, you can OR.
See the example statement below:
SELECT ProductCode FROM PricebookEntry WHERE CurrencyIsoCode = 'USD' or
CurrencyIsoCode = 'GBP'

Taking it a step further, you can evaludate multiple things in the WHERE
clause:
SELECT ProductCode,UnitPrice FROM PricebookEntry
WHERE (UnitPrice >= 10 and CurrencyIsoCode='USD')
OR (UnitPrice >= 5.47 and CurrencyIsoCode='EUR')

Chapter 6

LOOP IN SOQL

Unlike in SQL, the SOQL for loops iterate over ALL the sObjects returned by a
SOQL query.
Option 1 - Include the SOQL in the loop definition:

for (someVariables : [soql_query])

someCode
}

Note that the variables above must be of the same type as the sObject that
are returned by the soql_query. Here’s an example below of a simple for loop
function that uses the clauses WHERE and LIKE:
String s = ‘The Salesforce General’;

For ( Lead a : [SELECT Id, Name from Lead where Name = : (s)])

{
//someCode

Option 2 - Create a list of results (sObject list) first and then loop through
them

// Create a list of account records from a SOQL query

List<Account> accs = [SELECT Id, Name FROM Account WHERE Name = 'SalesforceGeneral'];
// Loop through the list and update the Name field
 for(Account a : accs){
a.Name = 'The Salesforce General';

Chapter 7

HOW TO JOIN CUSTOM OBJECTS

To join two custom objects in SOQL follow the example below:

businessAddress__c(Fields: city__c, state__c, country__c)

contract__c(Fields: Name)
select name, city__c, state, contract__c.name from businessAddress__c

Chapter 8

PREVENTING SOQL INJECTION

SQL/SOQL injection attacks are nasty and annoying. According to the Open Web
Application Security Project (OWASP), injection attacks are number one on the
2010 list of threats to be aware of in web application development. Injection
flaws occur when untrusted data is sent straight to the interpreter as part of a query
or command. In this case, the attacker is deliberately passing hostile data with the
intent of tricking the interpreter into executing unintended commands or gaining
access to unauthorized data.
With Salesforce development, we need to pay very special attention
to SOQL injection. Organizations are putting their most trusted information into
Salesforce with the understanding that the data is secure. Company financials,
customer lists, products, employees are all examples of very sensitive data to a
company. As a developer of Salesforce applications, part of your job is to uphold
the security put in place by the Force.com platform. A big part of this is making
sure that the custom code you write cannot be used to cause any hard to the
organizations using your code.
Salesforce.com provides great materials to read about SOQL Injection. I
recommend studying that material in detail. This posting is not meant to be a
comprehensive resource on the topic but rather a secondary source of information
to complement what is already available by Salesforce.com and OWASP.
Example of a vulnerable SQL call:

String sQuery = "SELECT * FROM customers WHERE accountID = "'" + request.getParameter("id")
+ "'";

In this case, the attacker can modify the "id" parameter in their browser
address bar or through a program they wrote to send: ' or '1'=1. This would
change the meaning of the query - resulting in all of the records from the customer
database to be returned instead of only the intended customer.
http://exampleofsqlinjection.com/customerView?id=' or '1'=1
Again, this is a very simple example. When you are developing in
Salesforce.com you need to be very careful when using any dynamic
SOQL.Dynamic SOQL is the creation of SOQL statements on he fly during the
execution of Apex code. An example of dynamic SOQL would be building a
custom search based on input from a form that the user has filled out. Dynamic
SOQL is very powerful and convenient but from a security standpoint, it is
something to approach with caution and care.
To create a dynamic SOQL query at runtime you will use the
database query method provided. Below are two examples of building dynamic
SOQL at runtime (both of these are taken from this page).
Return a single sObject when query comes back with one record:

sObject S = Database.query(string_limit_1);
Return a list of sObjects when query comes back with more than one record:

List<sObject> L = Database.query(string);

What you want to make sure of is that when you construct the actual query
you use the escapeSingleQuotesmethod. This method is a major time saver and
adds the escape character \ to al single quote marks in strings that are passed in
from the user. This basically takes a single quote mark and treats them as
enclosing strings rather than database commands. It's pretty slick and very
important!
Take a look at this example of SOQL Injection Vulnerability in Apex ( taken
from this SF Trailhead
article https://trailhead.salesforce.com/en/content/learn/trails/force_com_dev_intermediate
In this example, lets assume that there is a single input field where the user
enters a name. The name is then used to find all Contacts with that name that have
not been deleted.

 String qryString = 'SELECT Id FROM Contact WHERE (IsDeleted = false and Name like \'%' +
name + '%')';

Now, under normal operation the user would enter something like
"salesforcegeneral" and the resulting string would be:

qryString = SELECT Id FROM Contact WHERE (IsDeleted = false andName like
'%salesforcegeneral%')

The problem comes when a malicious user types in something like test%')
or name like ' In this case, we'd end up with something like this:

qryString = SELECT Id FROM Contact WHERE (IsDeleted = false and Name like '%test%') or
(Name like '%')

The result of this query is not just deleted contacts but ALL contacts. The
Salesforce documentation gives you a great method to combat this - avoid
dynamic SOQL and user static queries with variables. Here is an example of how
the above vulnerability is re-written:

String queryName = '%' + name + '%';

queryResult = [SELECT Id FROM Contact WHERE (IsDeleted = false and Name like :queryName)];

Chapter 9

SOQL IN A NUTSHELL

– SOQL (Salesforce Object Query Language) retrieves the records from the
database by using a “SELECT” keyword.
– By using SOQL, we can know in which object or fields the data resides.
– We can retrieve the data from a single object or from multiple objects that
are related to each other.
– SOQL uses the SELECT statement combined with filtering statements to
return sets of data, which may optionally be ordered:
SELECT one or more fields
FROM an object

WHERE filter statements and, optionally, results are ordered

SOQL query is enclosed between square brackets. The following query

retrieves a record from a database that has the name field value equal to
“Airway”.
Account a = [Select ID, Name from Account where Name=’acc1′];
In the above query “a” variable stores the ID, Name of the all accounts with
name “acc1”
– SOQL statements evaluate to a list of SObject records, single SObject
records or an integer for count method queries.
Querying Single SObject records: Below query gets single account record
with a given ID.
Account a = [Select Name from Account where ID=’XXXXXXXXXXXX’];
Querying List of Sobjects records: Following example is to display list of
account.
// querying list of records and stores in list variable “acc”

List<Account> acc = [Select Name from Account where industry = ‘education’];

System.debug(‘Size: ‘+acc.size()); // displays size of the list
for(Integer i=0; i<=acc.size();i++)

{
System.debug(‘Size: ‘+acc[i].Name); // To display accounts stored in “acc”
}

Below query gets number of record in an object.

Integer i = [Select count() from Account where industry=’education’];

System.debug(‘Count: ‘ + i);

Chapter 10

SOQL BASIC SYNTAX

• Object : When we mention Object it denotes Custom and Standard

Objects.
• Fields : When we mention fields it uses API names of an Object.
• Condition : To filter records we use conditions in SOQL.
• Ordering : This is used for Ordering the results. This may be
optional in SOQL.
• Limit : It Limits the number of records when fetching.
• FOR VIEW : This is used to fetch records from last day fetched
records.
• FOR REFERENCE : To update LastReferencedDate this is used.
• OFFSET : To denote the starting row of the record this is used.
• UPDATE VIEWSTAT : This updates the articles view statistics
for fetched records.
When writing SOQL statements we use API names for fields and labels of
the fields should not be used.
Standard Objects : Field Name refers as API name.
Custom Objects : API name column refers to API name.



Chapter 11

HOW TO WRITE SOQL

STATEMENTS

In this example, we are taking a Standard Object called “Account“. An account is

a standard object where we store information about our customers and partners.
• SELECT id, Name FROM Account

From above SOQL statement, we fetching Id and name of the Standard

object called “Account”.
We can fetch both Custom fields and Standards fields in standard objects.
• SELECT name, id, Active__c FROM Account

We have now successfully fetched both standard and custom fields from the
Account object. Custom fields with extension __c.
• SELECT name, s1__c, s2__c, s3__c, country__c FROM user__c

From above example student is the custom object so we have given name
as user__c and the custom fields called s1__c, s2__c, s3__c and country__c.
Name field is the Standard field in above example.
In Salesforce.com every object has system fields or standard fields. These
system fields have read-only property.
List of system fields are:
• Id
• IsDeleted
• CreatedById
• CreatedDate
 • LastModifiedById
• LastModifiedDate
• SystemModstamp



Chapter 12

ERROR
“SYSTEM.LIMITEXCEPTION: TOO
MANY SOQL QUERIES: 101”

“System.LimitException: Too many SOQL queries: 101” error occurs when you
exceed SOQL queries governor limit.

The actual limit is “you can run up to a total 100 SOQL queries in a single call or
context”.
• Change your code by following apex code best practices so that
the number of SOQL fired is less than 100.
• If you need to change the context, you can use @future annotation
which will run the code asynchronously.
Apex runs on a multi-tenant platform, the Apex runtime engine strictly
enforces limits to ensure code does not monopolize shared resources.
• Avoid SOQL queries in for loops.
• Fallow Apex code key principals while writing Triggers and bulk
requests.
Reference for Apex code best practices check the article "Apex Code Best
Practices" at https://developer.salesforce.com/page/Apex_Code_Best_Practices



Chapter 13

INNER AND OUTER JOINS

In SOQL statements, Inner and Outer Joins are used to join related objects like
parent object and child objects to retrieve all related fields from base objects
including the records of not refer to a related object.
An example:
• SELECT NAME, ACCOUNT__r.NAME FROM PROJ__C

From above SOQL statement, we are trying to retrieve Name and Account
name from Project object(custom object).
SOQL statement consists of a single base object and it is specified using the
keyword called “FROM”. Here we using Force.com IDE’s schema explorer to run
SOQL statements.
SOQL Inner Join statements are used to eliminate the records which records
are not matched with related objects.



Chapter 14

ALIAS NOTATION IN SOQL

Alias notation in SOQL statements is used to distinguish different object used in a

single SOQL Statement. They are different types of SOQL reserved keywords
that can not be used in alias names or alias notations in SOQL. Some of the alias
names are.

1. AND.
2. ASC
3. DESC.
4. EXCLUDES.
5. FIRST.
6. FROM.
7. GROUP.
8. HAVING.
9. IN.
10. INCLUDES.
11. LAST.
12. LIKE.
13. LIMIT.
14. NOT.
15. NULL.
 16. NULLS.
17. OR.
18. SELECT.
19. WHERE.
20. WITH
Let us see an example to understand alias notations in SOQL:
• SELECT Acct.Id, Acct.name FROM Account Acct

From above SOQL statement, we fetching Account Id, Account name

records from the Standard object called Account. we have mentioned the word
“ Acct”. “Acct” is the alias name for “Account”. We can fetch data directly
without using an alias name in order to understand alias names in SOQL we are
using alias names in this example:
• SELECT Firstname, lastname FROM Contact Con, Con.Account
Acct WHERE Acct.name= ‘RecordA‘

From above SOQL Statement Acct is the alias name for “Account” Con is
the alias name for “Contact”. We are fetching firstname and lastname from two
objects called Contacts and Account.
In both contacts and account, we have firstname and lastname, WHERE is the
condition to get the details from the specific record called “RecordA”. From
above SOQL statement, we fetching lastname and firstname from the Account and
Contacts of RecordA. These alias names are helpful when writing SOQL queries
for multiple objects. When writing SOQL statements for multiple objects many
fields are common in all the objects so the alias names are helpful to distinguish
the difference between the objects.



Chapter 15

WHERE CLAUSE

WHERE clause is also called as Conditional Expression. WHERE clause is used

to filter the retrieved data.
Whenever if we want to filter data from a set of object records we use
WHERE clause in SOQL. This WHERE clause filters the data based on the given
Condition or Criteria. Basically when WHERE clause in SOQL statement is used
WHERE clause is followed by some comparison operators and Logical Operators
like IN operator, NOT IN operator, INCLUDES operator, EXCLUDES Operator.
Let us see an example on WHERE clause:
• SELECT firstname, lastname FROM Contact WHERE firstname != null

From above SOQL statement, we are fetching data From Standard object
called “Contact”. We are using the conditional expression “WHERE” to filter the
retrieved data from the Contact object. Preceding WHERE we have
used != operator to filter the data firstname which is not null.



Chapter 16

NOT EQUALS OPERATOR

Let us see an example using Not equals operator to implement SOQL Statements.
Using Not Equals ” != “ data can be retrieved based on the condition or criteria.
• SELECT firstname, lastname FROM Contact WHERE firstname != ‘Mark‘

From the above SOQL statement, the date is retrieved from all the contacts
whose first name not equals to Mark.


Chapter 17

EQUALS OPERATOR

Using Equal Operator in SOQL can retrieve the data based on criteria and
condition equals “=”.
• SELECT Firstname, Lastname FROM Contact WHERE firstname = ‘Mark‘

From above SOQL statement the equals ” = ” checks whether the values of
the two operands are equal. If the value is equal, the condition becomes ” TRUE”.
If not the condition Fails.


Chapter 18

LESS THAN OR EQUAL TO

OPERATOR

Using the Less than or Equal operator we can retrieve records that are less than or
equal to the given limit.
• SELECT name, Fee_paid__c FROM student__c WHERE fee_paid__c <= 1000

Less than or equal to operator is used to check whether the values of the left
operand is less than or equal to the value of the right operand. If the condition
satisfies the condition will be TRUE.
From above SOQL statement, the students who paid fee less than or equals
to 1000 will be filtered.



Chapter 19

NOT IN OPERATOR

SOQL NOT IN operator is used to specify multiple values in the WHERE clause
for unmatching and filtering records. This operator is used to fetch data which are
not matched with the values specified in the statement.
• SELECT firstname, lastname FROM Contact WHERE firstname NOT IN ( ‘Mark’,
‘Brian’ )

In this example, we have used NOT IN Operator by using this operator the
data is fetched other than Mark and Brian from contacts.


Chapter 20

IN OPERATOR

In Salesforce Object Query Language IN Operator is used to specify multiple

values in the WHERE clause for matching and filtering records.
When We use IN operator in SOQL the data is fetched from the matched
values specified in the SOQL statement.
An example on SOQL IN Operator:
• SELECT Firstname, Lastname FROM Contact WHERE Firstname IN ( ‘Mark’, ‘Brian’ )

Data is retrieved from the records whose first name is Mark and Brian.
Values inside the braces are case insensitive.



Chapter 21

ORDER BY CLAUSE

ORDER BY Clause is used to retrieve the data in “Ascending” or “Descending”

order by the condition given in SOQL Statement.
In Salesforce ORDER BY clause is two types.
1. ASC.
2. DESC.

Here ASC means ascending and DESC means descending order. In SOQL by
default, the data will be retrieved in ascending order only.



Chapter 22

ORDER BY CLAUSE ASC

• SELECT name, Amount FROM opportunity ORDER BY name ASC

Here we have used ORDER BY Clause called ASC. From above SOQL
statement name and Amount values will be retrieved from Opportunity and
records will be arranged in Ascending order.



Chapter 23

INCLUDES, EXCLUDES
OPERATORS

SOQL Includes and Excludes operators are mainly used to filter multi picklist
field values in salesforce. These operators are used for only multi picklist values.
Multipicklist values are those who have more than one picklist values.
For example, a student must have many skills to get a job (programming,
attention to details, etc.)
• SELECT name, country__c, Student_skills__c FROM Student__C

From above SOQL statement all the records from Student object has retrieved.
INCLUDES operator
Includes operator is used to filtering and retrieve the data that contains any
of the specified values in SOQL statement.
• SELECT name, country__c,
Student_skills__c FROM Student__C WHERE student_skills__CINCLUDES (‘salesforce’)

EXCLUDES operator
• SELECT name, Country__C,
Student_skills FROM Student__c WHERE student_skills EXCLUDES(‘salesforce’)

From above SOQL statement, we fetching the data whose student will have
no salesforce skills. the condition must be written in single quotations and braces
as shown above.



Chapter 24

LOGICAL OPERATORS

SOQL Logic Operators are the connectors for connecting one or more conditions
in a Single SOQL statement. These SOQL logical operators are very easy to use to
check multiple conditions in a single Salesforce Object Query language statement.
There are two types of SOQL Logical operators in salesforce.com.
1. Operator AND
2. Operator OR
Logical Operator “AND” is used to retrieve the records when all the
conditions in SOQL statement are satisfied. If any of the condition in SOQL
statement is not satisfied records will not be retrieved.
• SELECT name, Amount FROM opportunity WHERE Amount > 100 AND Amount <
1000

Chapter 25

LOGICAL OPERATOR OR

Logical operator OR is used to retrieve the data if any of the condition

in SOQL statement is satisfied. SOQL logical operator “OR” matches the first
condition with second condition and data will be retrieved.

SELECT name, Amount FROM opportunity WHERE name = ‘express logistics‘ OR name =
‘MarkWhite SLA’

Chapter 26

FOR REFERENCE CLAUSE

The SOQL FOR REFERENCE clause is used to find Date or Time values when a
record has been referenced. The LastReferencedDate field is automatically
updated for any retrieved records.
When coming to FOR REFERENCE Clause, this is used to tack Date an
Time of the record when a record has been referenced last while executing a
SOQL query.
• SELECT city__c,
state__c, lastreferenceddate FROM Employee__C FOR REFERENCE
When we execute the above SOQL statement all the records will show the
same date and time.



Chapter 27

GROUP BY ROLLUP CLAUSE

SOQL GROUP BY ROLLUP Clause is used to add subtotals to get aggregates data
in the query results. Both GROUP BY and GROUP BY ROLLUP Clause are
similar but SOQL GROUP BY ROLLUP Clause returns multiple levels of
subtotal rows. When using GROUP BY ROLLUP Clause in a statement we can
add up to three fields in a comma-separated list.
• SELECT city_c, State_c, COUNT(Employee_Name__C)
Counts FROM Employee__C GROUP BY ROLLUP (City__c, State__C)
From above SOQL statement Grouped the data according to State, city and
also w calculates the number.




Chapter 28

ADVANCED SOQL STATEMENTS

We will cover the basics of SOQL escape sequences, SOQL date formats and
SOQL date literals.

1. Escape Sequence
An escape sequence character is a character that invokes an alternative
interpretation of the subsequent characters in a character sequence. Some of the
escape sequence characters are as follows.
1. \n or \N – New line
2. \r or \R – Carriage return
3. \t or \T – Tab
4. \f or \F – Form feed
5. \b or\B – Bell
6. \” – One double-quote character
7. \’ – One single-quote character
8. \\ – Backslash
9. The LIKE operator expression: \_ – It matches a single
underscore character (_)
10. The LIKE operator expression: \% – Matches s single percentage
a sign character (%)

2. SOQL data formats
 When SOQL statements we should follow some date formats which are
predefined in salesforce. There are four types of date formats in Salesforce object
Query language.

DATE FORMAT
EXAMPLES
YYYY-MM-DD
2014-06-28
YYYY-MM-DD Thh:mm:ss+hh:mm
2014-06-28 T23:01:01+01:00
YYYY-MM-DD Thh:mm:ss-hh:mm
2014-06-28 T23:01:01+01-08:00
YYYY-MM-DD Thh:mm:ssZ
2014-06-28 T23:01:01Z

3. SOQL Date literals
There are different types of SOQL Date literals are present when querying
the records using the date fields in the SOQL statements the date literals can be
used. Some of the Date literals are:
1. YESTERDAY
2. TODAY
3. TOMORROW
4. LAST_WEEK
5. NEXT_WEEK
6. LAST_MONTH
 7. THIS_MONTH
8. NEXT_MONTH
9. LAST_90_DAYS
10. NEXT_90_DAYS
11. LAST_N_DAYS:n
12. NEXT_N_DAYS:n
13. THIS_QUARTER
14. NEXT_QUARTER
15. NEXT_N_QUARTER:n
16. LAST_N_QUARTERS:n
17. THIS_YEAR
18. LAST_YEAR
19. NEXT_YEAR
20. NEXT_N_YEARS:n
21. LAST_N_YEARS:n
22. THIS_FISCAL_QUARTER
23. LAST_FISCAL_QUARTER
24. NEXT_FISCAL_QUARTER
25. NEXT_N_FISCAL_QUARTERS:n
26. LAST_N_FISCAL_QUARTERS:n
27. THIS_FISCAL_YEAR
28. LAST_FISCAL_YEAR
29. NEXT_FISCAL_YEAR
30. NEXT_N_FISCAL_YEAR:n
31. LAST_N_FISCAL_YEARS:n





Chapter 29

SOQL FOR VIEW CLAUSE

FOR VIEW Clause is used to find the date of the record when it has been last
viewed. Every record will have a standard field called LastViewedDate. This
field will be updated for every retrieved record.
FOR VIEW clause is used to track the date when the records were viewed
last while executing a SOQL Query.
• SELECT City_c, State_c,
lastViewedDate FROM Employee__C FOR VIEW
When the above statement is executed for the first time it shows the last
viewed date and when the SOQL statement is executed for the second time all the
records will show the same date and time for lastVieweddate.



Chapter 30

SOQL FUNCTIONS, GROUP

FUNCTIONS

When writing SOQL statements to fetch data from salesforce.com we use many
SOQL Functions like Group functions and Having clause.
Salesforce Object Query Language has many built-in functions to perform
various manipulations to fetch data from salesforce.com. SOQL functions reduce
the code and help to simplify complex SOQL statements.
• Group By Class: Group By Class is used to divide the total
number of data into groups based on criteria. It allows classifying the data into
groups based on criteria.
• Having Class: Having Class is used to specify the search
condition.

In SOQL Group functions are used to group set of records by the values specified
in the filed. This function will gather all the data in the specified fields and also
allow to perform aggregated functions.

◦ SELECT Continent__c, Country__c, Avg ( Average__c

) FROM Student__c Group By Continent__c, Country__c

• List of Aggregated functions in Group Function.

1. COUNT ( )
2. COUNT ( FIELD_NAME )
3. COUNT_DISTINCT ( )
 4. SUM ( )
5. MIN ( )
6. MAX ( )

• Above aggregated functions are used in Group By clause to fetch

data from Salesforce objects.

COUNT ( ) method is used to find the total number of records in an object that
matches the specified condition.

◦ SELECT COUNT ( ) FROM Student__c

•
COUNT ( FIELD_NAME ) Method is used to the total number of records of
particular value in the specified value.

◦ SELECT City__c, COUNT ( Employee_name__c ) FROM

Employee__C Group By City__C
•

COUNT_DISTINCT() Method is used to find the total number of distinct non-null

field values. This method ignored the null values and returns non-null values.

◦ SELECT COUNT_DISTINCT (City__C), TotalCities FROM

Employee__c

The MIN() method in SOQL is used to return the lowest values.

• SELECT MIN ( Average__c) FROM Student__C

The MAX () method is used to return the maximum or the largest value.
 • SELECT MAX (Average__C) FROM Student__c

HAVING Clause is SOQL is used to apply a condition based on a group
condition. It is very similar to the WHERE clause.

• Having Clause is used for only aggregated functions

• It is used to specify the search condition
• It limits the grouped records




Chapter 31

SOQL RELATIONSHIPS

SOQL Relationships between objects are mainly used to query the records from
one or more objects in a single SOQL statement in Salesforce.com.
Different SOQL Relationships:
1. SOQL Relationships between Standard objects
2. SOQL Relationships between custom objects
Many-to-one relationships: (n:1)

Parent-to-Child Relationship.
1. When a relationship is from parent-to-child then SOQL statement
must have sub Query in its Statement.
2. For standard objects, SOQL relationships name is equal
to the plural of the Child Object name.
• SELECT name, ( SELECT Lastname FROM CONTACTS ) FROM Account
Child-to-Parent Relationship.
1. When a SOQL Relationship is from Child-to-Parent then the
SOQL statement will not have Sub Query.
2. SOQL Relationships name is equal to the name of the parent
object.
• SELECT contact.firstname, contact.Account.name FROM Contact.

Many-to-one relationships: (n:1)

These relationships are specified in SELECT, FROM, WHERE clauses

using dot(.) operator.
• SELECT id, Name, Account.name FROM Contact WHERE Account.Industry = ‘
Media’

Parent-to-Child Relationship
• SELECT name (SELECT lastname FROM Contacts) FROM Account

SOQL Relationships between custom objects.

• SELECT Lastname__c, ( SELECT Lastname__c FROM Daughters__r
) FROM Mother__c

Child-to-Parent relationship.
• SELECT Id, Firstname__c,
Mother__r.firstname__c FROM Daughter__c WHEREMother__r.Lastname__c LIKE ‘c%’