+ Start a Discussion
akstakst 

Help with SOQL SUM and VisualForce

Hi - I am new to APEX, I have a query that looks like this. What am I doing wrong and how do I write a test case.

 

Thanks

 

 

public class GroupedQuery
{
    public string GroupBy1     {get;set;}
    public string GroupBy2     {get;set;}
    public string GroupBy3     {get;set;}
    public integer Count {get;set;}
    public Decimal Sum {get;set;}
    public Decimal Average {get;set;}
    public Decimal Min {get;set;}
    public Decimal Max {get;set;}

public List<GroupedQuery> queryResults {get;set;}

public List<sObject> queries = [Select b.Period__r.Name Name, SUM(b.Amount__c) Amount From Bud__c b group by Period__r.Name];
 {                                          
queryResults = new List<GroupedQuery>();
for (sObject query: queries)
{
    GroupedQuery myObject = new GroupedQuery();
    myObject.Count = Integer.valueOf(query.get('Amount'));
    myObject.GroupBy1 = String.valueOf(query.get('Name'));
    queryResults.Add(myObject);
}   }   }    

 

 

Best Answer chosen by Admin (Salesforce Developers) 
sravusravu

For using SUM,MIN,MAX,etc, you need to use aggregate result queries. Try the following code

 

public class GroupedQuery{  
    public list<AggregateResult> lstAr = new list<AggregateResult>();

    public GroupedQuery(){
        lstAR = [Select Period__r.Name Name, SUM(Amount__c) Amount From Bud__c group by Period__r.Name];
    }

    public list<BudClass> getResults(){
        list<BudClass> lstResult = new list<BudClass>();
        for(AggregateResult ar : lstAr){
            BudClass objBudClass = new objBudClass(ar);
            lstResult.add(objBudClass);
        }
        return lstResult;
    }
   
    class BudClass{
        public Double Amount {get;set;}
        public String Name {get;set;}
       
        public BudClass(AggregateResult ar){
            Amount= (Double) ar.get('Amount');
            Name = (String) ar.get('Name');
        }
    }

    public static testMethod void testGroupedQuery(){

                      GroupedQuery gq = new Grouped Query();

                      gq.getResults();

    }
}

 

Let me know if you face any difficulty..................

Hope this helps you.

 

All Answers

sravusravu

For using SUM,MIN,MAX,etc, you need to use aggregate result queries. Try the following code

 

public class GroupedQuery{  
    public list<AggregateResult> lstAr = new list<AggregateResult>();

    public GroupedQuery(){
        lstAR = [Select Period__r.Name Name, SUM(Amount__c) Amount From Bud__c group by Period__r.Name];
    }

    public list<BudClass> getResults(){
        list<BudClass> lstResult = new list<BudClass>();
        for(AggregateResult ar : lstAr){
            BudClass objBudClass = new objBudClass(ar);
            lstResult.add(objBudClass);
        }
        return lstResult;
    }
   
    class BudClass{
        public Double Amount {get;set;}
        public String Name {get;set;}
       
        public BudClass(AggregateResult ar){
            Amount= (Double) ar.get('Amount');
            Name = (String) ar.get('Name');
        }
    }

    public static testMethod void testGroupedQuery(){

                      GroupedQuery gq = new Grouped Query();

                      gq.getResults();

    }
}

 

Let me know if you face any difficulty..................

Hope this helps you.

 

This was selected as the best answer
akstakst

Thank you, it works :-)