SOQL Functions : In our previous salesforce tutorial we have learned about SOQL Group By Roll Up Clause. In this salesforce training tutorial we are going to learn about SOQL Function. 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 reduces the code and helps to simplify complex SOQL statements.
In this salesforce tutorial we will learn about two classes and functions. They are
- Group By Class : Group By Class is used to divide total number of data in to groups based on criteria. It allows to classify the data in to groups based on criteria.
- Having Class : Having Class is used to specify the search condition.
SOQL Group By Clause.
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.
- COUNT ( )
- COUNT ( FIELD_NAME )
- COUNT_DISTINCT ( )
- SUM ( )
- MIN ( )
- MAX ( )
Above aggregated functions are used in Group By clause to fetch data from salesforce objects.
COUNT ( ) Method.
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.
COUNT ( FIELD_NAME ) Method is used to the total number of records of a 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 the non-null values.
- SELECT COUNT_DISTINCT (City__C), TotalCities FROM Employee__c
MIN () Method
The MIN() method in SOQL is used to return the lowest values.
- SELECT MIN ( Average__c) FROM Student__C.
MAX () Method.
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 WHERE clause.
- Having Clause is used for only aggregated functions.
- It is used to specify the search condition.
- It limits the grouped records.
Example: List the students data Continent wise whose subject average is greater than 50.
- SELECT Avg(Average__c), Continent__c FROM Student__C Group By Continent__c HAVING Avg(Average__c) < 50