What about SOQL Aggregates in Salesforce?

Asked 02-Feb-2018
Viewed 397 times

1 Answer


1

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.