Aggregate Functions in Salesforce - The Developer Guide

In Spring ‘10, Salesforce released new Apex functionality for total functions in SOQL. These queries return an AggregateResult object. Utilize aggregate functions in a Group BY a clause in SOQL queries to produce reports for investigation.

Any query that incorporates an aggregate function It is used to return its results in an array of AggregateResult objects. AggregateResult may be a read-only sObject and is as it was utilized for query results.

You'll also utilize aggregate functions without employing a Group BY clause. For the case, you may utilize the AVG() total work to find the normal sum for all your account.

These functions incorporate AVG(), Tally(), COUNT(fieldName), COUNT_DISTINCT(), MIN(), MAX(), and Whole(). It is conceivable to utilize total capacities without employing a Group BY clause. However, the functions ended up an awfully capable device to produce reports once you utilize them with a Group BY clause.

Following Aggregate functions for GROUP BY clause:

• AVG() – It gives the average value of a numeric field.
• COUNT() – It gives the number of rows based on the query requirement.
• MIN() – It gives the minimum value of a field.
• MAX() – It gives the maximum value of a field.
• SUM() – it gives the total sum of a numeric field
• COUNT (FIELD_NAME)
• COUNT_DISTINCT ()

Don't forget to check out: Import Data With The Info Import Wizard | Salesforce Guide

AVG( ) :-

It gives the average value of a numeric field.

For example:

SELECT CampaignId, AVG(Amount) FROM Opportunity GROUP BY CampaignId

COUNT() and COUNT(fieldName) :-

It gives the number of rows based on the query requirement. For example:

USING COUNT( ):
SELECT COUNT( ) FROM opportunity  WHERE Name LIKE ‘a%’
USING COUNT(fieldName):
SELECT COUNT(Id) FROM opportunity  WHERE Name LIKE ‘a%’

COUNT_DISTINCT() :-

It gives the number of distinct non-null field values matching based on the requirement. For example:

SELECT COUNT_DISTINCT(Company) FROM Lead

One thing to be mindful of is that the count() function does not return an AggregateResult object. The coming about query result estimate field returns the number of columns

integer rows = [select count() from contact];
System.debug('rows: ' + rows);

MIN( ) :-

It gives the minimum value of a field. For example:

SELECT MIN(CreatedDate), FirstName, LastName FROM Account GROUP BY FirstName, LastName

In the event that you employ the MIN() or MAX() functions on a picklist field, the function uses the sort order of the picklist values rather than in sequential order arrange

MAX( ) :-

It gives the maximum value of a field. For example:

SELECT Name, MAX(BudgetedCost) FROM Campaign GROUP BY Name

Check out another amazing blog by Ayush here: How To Lock Records In Salesforce | The Complete Guide

SUM( ) :-

It gives the total sum of a numeric field. For example:

SELECT SUM(Amount) FROM Opportunity WHERE IsChecked  = Tri AND Probability > 70

Examples 1:-

List<aggregateResult> LstAR = [select leadsource, count(name) total,
 count(state) from lead group by leadsource ]; 
for (AggregateResult ar : LstAR )
 System.debug(ar.get('leadsource')+'-'+ar.get('total')+'-'+ar.get('expr0'));

Examples 2:-

AggregateResult[] ar = [SELECT AccountId, AVG(Amount) Avg FROM AccountId];
Object avgAmount = agResults[0].get(‘Average’);

Note:-

You can’t utilize a Limit clause in a query that employs aggregate work. The taking after query is invalid:

Examples 3:-

SELECT MAX(CreatedDate)
from Account limit 1