4

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.

2
  • what is datatype of unit_booked__c?
    – C0DEPirate
    Jun 10, 2016 at 14:29
  • Number field @C0DEPirate
    – Pavan tej
    Jun 10, 2016 at 14:30

1 Answer 1

4

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
];
5
  • Hey once again thanks bro you are the rock star you save me a lot of time.But if need any other field i need to go to write another query.@Adrian Larson
    – Pavan tej
    Jun 10, 2016 at 16:44
  • @Pavantej It is unclear, how would you even incorporate other data? You're summing up multiple records, so for example Name doesn't really have much meaning without more context.
    – Adrian Larson
    Jun 10, 2016 at 16:48
  • Ya you are right.But in my context Forecast_Units__c is a number so, for further requirements i need this field also.@Adrian Larson
    – Pavan tej
    Jun 10, 2016 at 17:01
  • On each individual record? Or as another sum? @Pavantej You can add it if you aggregate it.
    – Adrian Larson
    Jun 10, 2016 at 17:01
  • 1
    ya i understood @Adrian Larson thanks really you are one of the best developers in stack exchange.you really helping us beginners like me.i am new to development can you gave any suggestions.
    – Pavan tej
    Jun 10, 2016 at 18:22

You must log in to answer this question.

Not the answer you're looking for? Browse other questions tagged .