November 14, 2016 by LanceShi
2.5 Use aggregate statements
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
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.