Question

How do you write the Where clause in SOQL when GroupBy is used for aggregate functions?

Answer 1

In SOQL, the Where clause is used to filter records before aggregation and is written before the Group By clause, while the HAVING clause is used after the Group By clause to filter groups formed by the query based on the aggregate results.

Step-by-step explanation:

When using Group By in SOQL with aggregate functions, the Where clause is written before the Group By clause and it is used to filter the results of a query by certain criteria. The Where clause specifies the conditions that must be met for a record to be selected.

In SOQL, once you've aggregated data using functions like COUNT(), SUM(), AVG(), etc., you can further filter the groups returned by the query with the HAVING clause. The HAVING clause is similar to the Where clause but is used after aggregation to filter groups, whereas the Where clause filters individual records before aggregation.

Example query with Group By and a Where clause:

SELECT COUNT(Id), AccountId
FROM Opportunity
WHERE Amount > 50000
GROUP BY AccountId
HAVING COUNT(Id) > 1

This query selects the number of opportunities and groups them by AccountId for those opportunities where the Amount is greater than 50,000 and filters out the groups with less than two records.