+ Start a Discussion
hal9001hal9001 

aggregate results cannot be used in a batch query

I'm trying to save the following custom list controller, but I' getting the error:  aggregate results cannot be used in a batch query

 

public class summaryListCon {
// ApexPages.StandardSetController must be instantiated
// for standard list controllers
public ApexPages.StandardSetController setCon {
get {
if(setCon == null) {
setCon = new ApexPages.StandardSetController(Database.getQueryLocator(

[SELECT 
Trans__c.Transaction__r.Equipment__r.Name Equipment
,Trans__c.Location__r.Location_Type__r.Name LocType
,Trans__c.Location__r.Account__r.Name  Account
,Trans__c.Location__r.Name Location
,SUM(Qty__c) Qty,Sum(Product_Days__c) Product_Days
from Trans__c
GROUP BY
Trans__c.Transaction__r.Equipment__r.Name
,Trans__c.Location__r.Location_Type__r.Name 
,Trans__c.Location__r.Account__r.Name 
,Trans__c.Location__r.Name
]

));
}
return setCon;
}
set;
}
// Initialize setCon and return a list of records
public List<SObject> getSummary() {
return (List<SObject>) setCon.getRecords();
}
}

 How can I use aggregate results on a VisualForce page?

Thanks!

Best Answer chosen by Admin (Salesforce Developers) 
hal9001hal9001

Here is the solution that worked for me:

 

the controller:

public class locationBalances  
{  
    public list<AggregateResult> lstLB = new list<AggregateResult>();  
    public locationBalances()  
    {  
        lstLB = [
            SELECT Trans__c.Transaction__r.Equipment__r.Name Equipment
                    ,SUM(Qty__c) Quantity
                    from Trans__c
                    GROUP BY Trans__c.Transaction__r.Equipment__r.Name
            ];  
    }  
  
    public list<LBClass> getResults()  
    {  
        list<LBClass> lstResult = new list<LBClass>();  
        for (AggregateResult lb: lstLB)  
        {  
            LBClass objLBClass = new LBClass(lb);  
            lstResult.add(objLBClass);  
        }  
        return lstResult;  
    }  
    class LBClass  
    {  
        public Decimal Balance    { get;set; }  
        public String Equipment   { get;set; } 
          
        public LBClass(AggregateResult lb)  
        {  
            Equipment = (String)lb.get('Equipment');
            Balance = (Decimal)lb.get('Quantity');   
        }  
    }  
}

 and the page:

<apex:page controller="locationBalances">  
  <apex:pageBlock title="Location Balances">  
      <apex:pageBlockTable value="{!Results}" var="lb">  
          <apex:column headerValue="Equipment"  value="{!lb.Equipment}"/>         
          <apex:column style="text-align:right;" headerValue="Balance" value="{!lb.Balance}"/> 
      </apex:pageBlockTable>  
  </apex:pageBlock>  
</apex:page>

 

 

 

 

 

 

 

All Answers

hal9001hal9001

OK, so I need to use something like this:

AggregateResult[] groupedResults = [SELECT Name, Days__c FROM Contact WHERE Days__c != ];
hal9001hal9001

I think I'm making progress.  Now I'm getting the error: 
Error: Invalid field Equipment for SObject AggregateResult 

when I try to save this page:

<apex:page Controller="summaryCon">
    <apex:pageBlock >
        <apex:pageBlockTable value="{!summaryConList}" var="s">
        <apex:column value="{!s.Equipment}"/>
        <apex:column value="{!s.Quantity}"/>
        </apex:pageBlockTable>
    </apex:pageBlock>
</apex:page>

 and here is the controller:

public class summaryCon {
    
    List<AggregateResult> summaryConList = null;
    
    public summaryCon() {
        summaryConList = [
            SELECT Trans__c.Transaction__r.Equipment__r.Name Equipment,SUM(Qty__c) Quantity
            from Trans__c
            GROUP BY Trans__c.Transaction__r.Equipment__r.Name
        ];
    }
    
    public List<AggregateResult> getsummaryConList() { return summaryConList; }
    
}

 

I've tried referring to the Equipment field using the full name instead of the alias, but I get similar errors.

 

 

 

 

hal9001hal9001

Here is the solution that worked for me:

 

the controller:

public class locationBalances  
{  
    public list<AggregateResult> lstLB = new list<AggregateResult>();  
    public locationBalances()  
    {  
        lstLB = [
            SELECT Trans__c.Transaction__r.Equipment__r.Name Equipment
                    ,SUM(Qty__c) Quantity
                    from Trans__c
                    GROUP BY Trans__c.Transaction__r.Equipment__r.Name
            ];  
    }  
  
    public list<LBClass> getResults()  
    {  
        list<LBClass> lstResult = new list<LBClass>();  
        for (AggregateResult lb: lstLB)  
        {  
            LBClass objLBClass = new LBClass(lb);  
            lstResult.add(objLBClass);  
        }  
        return lstResult;  
    }  
    class LBClass  
    {  
        public Decimal Balance    { get;set; }  
        public String Equipment   { get;set; } 
          
        public LBClass(AggregateResult lb)  
        {  
            Equipment = (String)lb.get('Equipment');
            Balance = (Decimal)lb.get('Quantity');   
        }  
    }  
}

 and the page:

<apex:page controller="locationBalances">  
  <apex:pageBlock title="Location Balances">  
      <apex:pageBlockTable value="{!Results}" var="lb">  
          <apex:column headerValue="Equipment"  value="{!lb.Equipment}"/>         
          <apex:column style="text-align:right;" headerValue="Balance" value="{!lb.Balance}"/> 
      </apex:pageBlockTable>  
  </apex:pageBlock>  
</apex:page>

 

 

 

 

 

 

 

This was selected as the best answer