AggregateResult in Salesforce

AggregateResult in Salesforce is used to find Sum, Min, Max and Avg. It is similar to Aggregate function in SQL.

Using Aggregate Result, we cannot fetch data. It is mainly used to find Sum, Min, Max and Avg.

Sample Code:

Visualforce page:

<apex:page controller="Sample" sidebar="false" action="{!show}">
<apex:pagemessages />
<apex:form >
    <apex:pageBlock >
        <apex:pageblockTable value="{!SummaryList}" var="r">           
            <apex:column headerValue="Account Name" value="{!r.AcctName}"/>
            <apex:column headerValue="Number of Contacts" value="{!r.Total}"/>
        </apex:pageblockTable>
    </apex:pageBlock>
</apex:form>
</apex:page>


Apex Controller:

public with sharing class Sample {
    public List<AggregateResult> Result {get;set;}   
    public List<Summary> SummaryList {get;set;}
    public List<Account> AcctList;
    public Map<Id, Account> IdAccount;
    List<Id> Ids;
   
    public void show() {
        SummaryList = new List<Summary>();
        Result = new List<AggregateResult>();
        Ids = new List<Id>();
        AcctList = new List<Account>();
        IdAccount = new Map<Id, Account>();
       
        Result = [SELECT Count(Id) Total , AccountId FROM Contact WHERE AccountId != null GROUP BY AccountId];              
       
        for(AggregateResult a : Result) {  
            Ids.add((Id)a.get('AccountId'));
        }
       
        AcctList = [SELECT Name FROM Account WHERE Id IN : Ids];
       
        System.debug('Account List' + AcctList);
       
        for(Account a : AcctList) {
            IdAccount.put(a.Id, a);           
        }
       
        System.debug('Ids and Accounts are ' + IdAccount);
               
        for(AggregateResult a : Result) {
            Account TempAcct = new Account();           
            TempAcct = IdAccount.get((Id)(a.get('AccountId')));
            system.debug('Account Name is ' + TempAcct.Name);
            SummaryList.add(new Summary(a, TempAcct.Name));               
        }   
    }
   
    public class Summary {
        public Integer Total {get;set;}
        public String AcctName {get;set;}
       
        public Summary(AggregateResult a, String AccountName) {
            Total =  (Integer)a.get('Total');
            AcctName = AccountName;
        }
    }
}


Output:


Cheers!!!

6 comments:

  1. I have Create a Custom Object(Survey_Result__c) With Fields are Evaluated_Area_1__c, Evaluated_Area_2__c, Evaluated_Area_3__c, Evaluated_Area_4__c, Evaluated_Area_5__c.. 5 fileds. i get all fields Average using soql.. i have using all users list in vf page with checkboxes.
    if i click first checkbox user and display the if i click first user Survey Result Records are to be display with Avg. and then click second User Displayed frst user survey records and seecond user records both are averaged to be display.. whats the solution? if I get What user I click i can display the survey Result records.. Whats the Solution? tell the Answer..

    ReplyDelete
  2. Hi,
    I tried above code but a got below error
    System.NullPointerException: Attempt to de-reference a null object

    This error showing below loop

    for(AggregateResult a : Result) {
    Account TempAcct = new Account();
    TempAcct = IdAccount.get((Id)(a.get('AccountId')));
    system.debug('Account Name is ' + TempAcct.Name);
    SummaryList.add(new Summary(a, TempAcct.Name));
    }

    ReplyDelete
    Replies
    1. Please check now. I have fixed the null pointer exception.

      Delete
  3. Hi all I too got same error but i tried like this it is also not working properly
    arlist=[select Account.Name,count(AccountID) Total from contact group by Account.Name];
    AccountName=(String)ar.get('Account.Name');

    ReplyDelete
    Replies
    1. Please check now. I have fixed the null pointer exception.

      Delete
  4. if(IdAccount.containsKey((Id)a.get('AccountId'))){
    TempAcct = IdAccount.get((Id)(a.get('AccountId')));
    }

    ReplyDelete