2.5 Use aggregate statements

The concept

Aside from retrieving values from a specific field, SOQL can also retrieve values from the aggregated result. For example, we can retrieve how much does each customer spend on all the transaction he or she made. In order to do this, we need to utilise Group by and Having clause.

Group by example


Select Sum(Merchandise__r.Original_Price__c), Customer__r.Name
From Transaction__c
Group by Customer__r.Name

Group by means, if that field is of a same value, we can group them altogether. After grouping, we can use aggregate functions to gather some results. Those aggregate functions include:

Avg(): get the average value

Count(): count the number of rows

Min(): get the minimal value

Max(): get the max value

Sum(): get the summary

Having example:

Continued with the previous example:


Select Sum(Merchandise__r.Original_Price__c), Customer__r.Name
From Transaction__c 
Group by Customer__r.Name
Having Sum(Merchandise__r.Original_Price__c) > 100

Having clause can be understood as the where clause in aggregate situation.

We will talk more about the considerations of using group by clause in the next post.

Next Post

2.6 Group by considerations

Subscribe to Sfdcinpractice

Subscribe to get the latest blogs and tutorials of sfdcinpractice. No spam, no trash, only the awesome posts from sfdcinpractice. 

Leave a Reply

Your email address will not be published / Required fields are marked *