Getting Started with SOQL (2014)

Chapter 4. Functions in SOQL

SOQL has many built-in functions to perform manipulations using the retrieved data. This chapter deals with the usage of functions in SOQL. We will learn how these functions of SOQL reduce and avoid the usage of long SOQL statements. It is mainly used to simplify complex SOQL statements. These functions can just be called to perform repeated tasks.

Summarizing the records using the GROUP BY clause will also be explained with real-time examples. We will also explain all the aggregated functions and discuss how to filter the aggregated values using the HAVING clause.

Using the toLabel() method

The toLabel() method is used to convert the results of a particular field into the user's language. All organizations can use toLabel(). The toLabel() method is of great help to an organization whose Translation Workbench is enabled.

The toLabel() method is used to get the translated values. The translation will be done on the user's locale who is querying the records.

There are many limitations of using toLabel(). These limitations are discussed in detail in Chapter 5, Limitations and Best Practices. Keep these limitations while in mind using toLabel().

Note

The Translation Workbench is used to specify all the languages in which the configurations performed in your organization can be translated.

A sample query is given as follows:

SELECT Name, toLabel(Industry) FROM Account

The preceding query retrieves all the records from the Account object whose Industry field values will be displayed in the querying user's locale. The following screenshot shows us the output of the SOQL execution:

The output of the query shows us the Name and Industry column in the querying user's locale. If the Industry values were in different languages, we would have been able to see those values in the current user's locale. Organizations that have the Translation Workbench enabled can effectively make use of this feature.

Using the GROUP BY clause

So far, all the queries that we saw were used to retrieve the records that match the WHERE conditions. We can also summarize our records using the GROUP BY clause.

The GROUP BY clause is used to group the set of records by the values specified in the field. The GROUP BY clause will gather all of the records that contain data in the specified fields together and will allow aggregate functions to be performed on one or more fields.

The GROUP BY clause is used along with the aggregate functions to group the retrieved records using one or more fields. We can use a GROUP BY clause without an aggregated function to query all the distinct values, including the null values for an object. In order to avoid null values, COUNT_DISTINCT() is used. The COUNT_DISTINCT() usage is further discussed later.

The aggregate functions available in SOQL are as follows:

· COUNT()

· COUNT(FIELD_NAME)

· COUNT_DISTINCT()

· SUM()

· MIN()

· MAX()

The preceding six aggregate functions are used along with the GROUP BY clause in SOQL to fetch our required statistical data from the objects. These aggregate functions are very useful when the requirements need summarized or grouped values.

Let's see a sample query with the COUNT(FIELD_NAME) aggregate function. All the aggregate functions are discussed in detail with examples.

A sample query is given as follows:

SELECT City__c, Count(Employee_Name__c) FROM Employee__c GROUP BY City__c

The preceding query is used to find the number of employees in each and every city. It also fetches the number of records and shows us the count of employees whose City column value is null or blank. The following screenshot shows us the output of the preceding query execution:

Using the COUNT() method

The COUNT() method is used to find the total number of records that match the specified condition. The COUNT () method is also used to find the total number of records in an object. It is used to find the number of elements of a finite set of objects.

A sample query is given as follows:

SELECT COUNT() FROM Employee__c WHERE State__c = 'Tamilnadu'

Using the COUNT(Field_Name) method

The Count(Field_Name) method is used to find the total number of records of a particular value in the specified field. If we use Count(Field_Name), it finds the total for each and every value of that field name. For example, if we use Count(City__c), it will return the total number of records for each city.

The syntax of the query is given as follows:

SELECT COUNT(FIELD_NAME) FROM Object_API_Name

A sample query is given as follows:

SELECT City__c, Count(Employee_Name__c) FROM Employee__c GROUP BY City__c

The following screenshot shows us the output of the preceding query execution:

A sample query is given as follows:

SELECT Count(Id) TotalRecords FROM Employee__c

The preceding query is used to find the total number of records in an object. The following screenshot shows us the output of the preceding query execution:

The output shows us that the total number of records in the Employee object is 26. The total number of records in any object will be very useful when we create charts using the Visualforce charting.

Using the COUNT_DISTINCT() method

The Count_DISTINCT() method in SOQL is used to find the number of distinct non-null field values as mentioned in the query criteria. The COUNT_DISTINCT() method ignores the null values and returns the non-null values while querying.

The syntax of the query is given as follows:

SELECT COUNT_DISTINCT (FIELD_NAME) FROM Object_API_Name

A sample query is given as follows:

SELECT COUNT_DISTINCT(City__c) TotalCities FROM Employee__c

The preceding query is used to find the number of distinct cities the employees belong to. The following screenshot shows us the output of the preceding query execution:

The output shows us that there are five unique or distinct cities that the employees belong to.

Using the MIN() method

The MIN() method in SOQL is used to return the minimum or smallest value of the mentioned field. The MIN(x) method is used to return the minimum value of the x field.

The syntax of the query is given as follows:

SELECT MIN (FIELD_NAME) FROM Object_API_Name

A sample query is given as follows:

SELECT MIN(Age__c) MinAge, City__c FROM Employee__c GROUP BY City__c

The preceding query is used to find the minimum age of an employee in each and every city. It will not show us the values of employees if their City values are blank or null. The following screenshot shows us the output of the preceding query execution:

The output shows us the minimum age of an employee in a city.

Using the MAX() method

The MAX() method in SOQL is used to return the maximum or the largest value of the mentioned field. The MAX(x) method is used to return the maximum value of the x field.

The syntax of the query is given as follows:

SELECT MAX (FIELD_NAME) FROM Object_API_Name

A sample query is given as follows:

SELECT MAX(Age__c) MaxAge, City__c FROM Employee__c GROUP BY City__c

The preceding query is used to find the maximum age of an employee in each and every city. The following screenshot shows us the output of the preceding query execution:

The output shows us the minimum age of an employee in a city.

Using the SUM() method

The SUM() method is used to find the total of the specified numeric field. The numeric fields available in Salesforce.com are Currency, Percent, and Number. The SUM() method is used to add a sequence of numeric fields. The result of SUM() is their sum or total.

The syntax of the query is given as follows:

SELECT SUM (FIELD_NAME) FROM Object_API_Name

A sample query is given as follows:

SELECT SUM(Age__c) MaxAge, City__c FROM Employee__c GROUP BY City__c

The preceding query returns the total age of employees in each and every city. The following screenshot shows us the output of the preceding query execution:

Using the HAVING clause

The HAVING clause is very similar to the WHERE clause. However, the only difference between the HAVING and WHERE clause is that the HAVING clause is used only with the aggregate functions.

The HAVING clause is used to specify the search condition in the GROUP BY clause or the aggregate functions. The HAVING clause limits the grouped records returned by a SOQL statement. However, the WHERE clause limits the records returned by a SOQL statement.

A HAVING clause in SOQL is used to specify that the SOQL SELECT statement should only return the records whose aggregate values meet the specified conditions.

A sample query is given as follows:

SELECT City__c, COUNT(Employee_Name__c) FROM Employee__c GROUP BY City__c HAVING COUNT(City__c) >= 1

The following screenshot shows us the output of the preceding query execution:

The output of the SOQL execution shows us the number of employees in each and every city whose number of records in each and every city is greater than one. This condition ignores all the employees whose City is null. It also ignores the employees if the number of employees in a city is less than or equal to one.

Summary

In this chapter, we learned about all the functions that are available in SOQL. We discussed the method to translate the field values using toLabel(), which will be very useful when we want to translate the values and show them in a report.

Grouping or summarizing the records with aggregate functions was also discussed. The six aggregate functions were discussed with the syntax and real-time examples. We also discussed the situations in which we have to use the HAVING and WHERE clauses.