SOQL has Aggregate functions. The aggregate function works pretty much the way you expect them to. The big thing to be aware of when to work with aggregates is that for most of the functions our results are returned as an
AggregateResult type.
For example SUM() and MAX() is Aggregate functions, which allows us to roll up and summarize our data in a query.
We can use aggregate functions to find average Amount of our opportunities, below is an example:
AggregateResult[] aggregateResults = [SELECT AVG(Amount) aver FROM Opportunity];
Object avgAmount = aggregateResults[0].get(‘aver’);
And, some of the functions like count_distinct(), count(fieldname), min(), avg(), etc. returns AggregateResult object or a List of AggregateResult objects.
See an example, we are getting a record count for a particular table named Account in SQL, we do something like the following:
The query looks like in SQL:
SELECT COUNT(*) FROM Account
The query looks like in SOQL:
SELECT COUNT() FROM Account
Both Looking very similar, right? But, they are different and
depends on the version of the count function we use, because of both
return different things.
Explanation:
The COUNT() function which is without a field name is an older version and was available prior to other
aggregate functions. The COUNT() function returns an integer value
and same as the COUNT(*) function does in SQL.
Now, a Newer version of count() function is COUNT(fieldname)
which returns the number of rows where the fieldname has a non-null value. Now you can understand the difference, the difference is this will
return a list of AggregateResults, not a single value like the previous version.
See an example:
go to your Developer Console and click the Query Editor tab that is located at the bottom of the pane. And
delete the existing code and insert this code:
SELECT COUNT() FROM Account
Now, when you execute the query, the result shows the total number of rows with a number beside it.
Now, go back to the Query Editor tab and change the query with this:
SELECT COUNT(Id) FROM Account
Now, when you execute the query, the results show only one row
returned and a column that displays the total number of records.
Take another example:
Go to your Developer Console, and select Debug, and after click on Open Execute Anonymous Window, and delete the existing code, and insert the following code:
List<AggregateResult> results = [SELECT Industry, count(Id) total FROM Account GROUP BY Industry];
for (AggregateResult aggr: results) { System.debug('Industry: ' + aggr.get('Industry')); System.debug('Total Accounts: ' + aggr.get('total')); }
Now, notice how we are used an alias to represent the total along with the
GROUP BY clause. We can only alias fields in aggregate queries which uses the
GROUP BY clause.
But, make sure that your Open Log is selected, and after click on
Execute button. A loader will show your execution log. To see the debug statements in the log, select the Debug Only options.
That’s it.