how to use group by in soql with lists

Question

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.

Answer 1

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
];