How to use WHERE,GROUP BY, HAVING,ORDER BY in SOQL?

We need to be very careful while writing the SOQL query. We should always think that how will our query execute on bulk data.

Let's understand the usage of WHERE, GROUP BY, HAVING and ORDER BY [These are called as clauses].

All the above clauses are optional but it is very important to follow the order. Please see the above diagram for order.

What will happen if I change the order of the above clauses?
The Query will not work 😃😃

WHERE clause :

~~~~~~~~~~~~~~

In real-world, we will always work with partial data and we will not compare all database data in a single transaction.So, here comes the WHERE clause into the picture. We will try to fetch the data that is required(meaningful) to the current transaction.

GROUP BY clause:

~~~~~~~~~~~~~~~

1. To get the aggregate value for the result set received in WHERE clause, we use GROUP BY clause.

Invalid Query Example: 

List<AggregateResult> agr= [Select sum(AnnualRevenue),AccountNumber,Name from Account group by AccountNumber];

2. Fields that are not part of an aggregate (MAX, MIN, AVG, COUNT, etc) must be included in the GROUP BY.

Let us say when you're grouping the above query by AccountNumber, how do you select the Name if there are 2 people with the same AccountNumber but different Names?

3. Few Field datatypes are not supported for GROUP BY, execute the below code snippet in execute anonymous block.

For example: I am checking "AnnualRevenue(curency Datatype)" field of Account Object. 

DescribeFieldResult describeResult = Account.AnnualRevenue.getDescribe();
system.debug(describeResult.Groupable);

Output : False.

4. What are our next steps if a field is not supported in GROUP BY clause?

The solution for this scenario is to create a new supported field and update the unsupported values into supported field data types using workflow/Process builder/Triggers.

Note: CLICK HERE to check all properties of "DescribeFieldResult" class.

HAVING clause:

~~~~~~~~~~~~~~

In order to filter the data retrieved from GROUP BY, HAVING clause is used.

Confused with HAVING & WHERE 😕, see below:

  HAVING clause cannot be replaced with WHERE clause. The differences are:

• WHERE clause is used to filter initial data that is rretrieved from DB.
• HAVING is used to filter the results of GROUP BY clause.

ORDER BY:

~~~~~~~~~

1. ORDER BY is used when our Data needs to ordered in Ascending order or Descending order.
2. ORDER BY fields must be part of GROUP BY clause or we can use field with the aggregate function.

Example: Below 2 queries are valid

Select sum(AnnualRevenue) ,Name from Account  WHERE Name LIKE '%Test%' AND AccountNumber<>NULL GROUP BY Name,AccountNumber  having sum(AnnualRevenue)> 102 ORDER BY AccountNumber ASC

Select sum(AnnualRevenue) ,Name from Account  WHERE Name LIKE '%Test%' AND AccountNumber<>NULL GROUP BY Name,AccountNumber  having sum(AnnualRevenue)> 102 ORDER BY sum(AnnualRevenue) ASC

Real-Time Example :
There is a Hospital Object(table) and it has 2 types of rooms viz AC,Non-AC.
The requirement is to get the revenue generated for AC and Non-Ac rooms in the year 2017 ,
the revenue generated must be greater than 100 and the result must be in Ascending order.
Sample Data:

Query:
SELECT RoomType,sum(Amount) Revenue  FROM Hospital where ExitDate<2018-01-01 and ExitDate>2016-12-31 GROUP BY RoomType HAVING sum(Amount)>100 ORDER BY Amount ASC  

Output:

tHiNk gooD and dO thE bEsT.........MANJU NATH 🌝