[SalesForce] how to use group by in soql with lists

Hi friends i'm new to sfdc development I wrote a sample query to fetch opportunities related to account with some conditions try to group i getting error.

my code is:

List<Opportunity_Trial__c> opp=new List<Opportunity_Trial__c>();
List<Account_Trial__c> acc=new List<Account_Trial__c>();
List<String> AccountId=new List<String>();
acc=[Select Id,Account_Trial__c.Type__c,Account_Trial__c.name__c,
 Account_Trial__c.azuga_customer_id__c,accountid__c from Account_Trial__c];
for(Account_Trial__c acct:acc) 
{
AccountId.add(acct.accountid__c);
}
opp =[SELECT Name,Type__c,sfdc_account_id__c,close_date__c,
      Forecast_Units__c,Stage_Name__c from Opportunity_Trial__c 
      where sfdc_account_id__c In: AccountId AND (
      close_date__c >= 2016-05-01 and close_date__c <= 2016-06-01)];

My problem is when i try to to do group by the above query to like below.It asks me to add all the fields in grouping but i need only type & stage name.Even if i add all the fields in grouping it throws an error like "Forecast_Units__c" can not be grouped in a query call but it is a string.

AggregateResult[] results =[SELECT Name,Type__c,sum(Units_Booked__c),
        sfdc_account_id__c,close_date__c,Forecast_Units__c,Stage_Name__c 
        from Opportunity_Trial__c where sfdc_account_id__c In: AccountId    
        AND(close_date__c >= 2016-05-01 and close_date__c <= 2016-06-01)  
        GROUP BY Type__c,Stage_Name__c ];

Please any suggestion to resolve this problem.

Best Answer

Pretty descriptive message. Don't add any fields in your SELECT clause unless they are in your GROUP BY clause or you aggregate them.

AggregateResult[] results = [
    SELECT Type__c, Stage_Name__c, sum(Units_Booked__c),
    FROM Opportunity_Trial__c
    WHERE sfdc_account_id__c In: AccountId    
    AND(close_date__c >= 2016-05-01 and close_date__c <= 2016-06-01)  
    GROUP BY Type__c,Stage_Name__c
];

Related Solutions

[SalesForce] Split a single field into 2 columns for a VF page

This is untested so you may need to have play, however this should return a List of the RevenueWrapper classes which contains the 4 columns you are looking to use in your VF page:

public class RevenueController{

Map<String, RevenueWrapper> ResultList = new Map<String, RevenueWrapper>(); 

public RevenueController(){
    List<AggregateResult> result =  [
    SELECT FISCAL_YEAR(Date__c) Date, Type__c, sum(Amount__c) Amount
    FROM Revenue__c
    GROUP BY FISCAL_YEAR(Date__c), Type__c];

    for(AggregateResult ar : result){

        RevenueWrapper RW = new RevenueWrapper();

        //If the RevenueWrapper already exists grab it from the Map
        if(ResultList.containsKey(String.valueOf(ar.get('Date')))){
            RW = ResultList.get(String.valueOf(ar.get('Date')));
        }

        //Set the amount
        RW.setAmount(ar);

        //Save back to the Map
        ResultList.put(String.valueOf(ar.get('Date')), RW);
    }
}

public List<RevenueWrapper> getResults() {
    return ResultList.values();
}


public class RevenueWrapper{
    public String revenueDate { get; set; }
    public Integer budgetAmount { get; set; }
    public Integer actualAmount { get; set; }
    public Integer varianceFYPercent { set; }
    public Integer varianceFYAmount { set; }

    public RevenueWrapper(){

    }

    public void setAmount(AggregateResult ar){
        revenueDate = String.valueOf(ar.get('Date'));

            //Set the amount value based on type
        if(ar.get('Type__c') == 'Budget'){
            budgetAmount = Integer.valueOf(ar.get('Amount'));
        }else if(ar.get('Type__c') == 'Actual'){
            actualAmount = Integer.valueOf(ar.get('Amount'));
        }else{ //Catch all for a type that isn't recognised
            return;
        }

    }

    public Integer getVarianceFYAmount(){
        return actualAmount - budgetAmount;
    }

    public Decimal getVarianceFYPercent(){
        if(budgetAmount != 0){
            return Decimal.valueOf((Double.valueOf(actualAmount) / Double.valueOf(budgetAmount)) * 100).setscale(1);
        } else{
            return 0;
        }
    }
}

}

[SalesForce] Filter and search is not working

I see that the code where you find actual accounts is not returned in wrapper format

    public pagereference Search()
    {
        String finalSearchValue = '%' + fullname + '%';
        Accounts = [Select Id, Name, Active__c FROM Account
                    where Name like :finalSearchValue];
        wraplist = new list<wrapData>();  
        for(Account a: Accounts)
        {
            wrapList.add(new wrapData(a));
        }
       return null;
   }

Same way apply for filter method as well.

Related Topic