1

Hi i have written this query but it will give earror(Field must be grouped or aggregated:) [SELECT Profile__c,name,count(NumberofLogin__c),Startdate__c,EndDate__c FROM User__c where GROUP BY PROFILE__c];

1

your group by clause should contain all the columns listed in the select statement with the exception the column included in the aggregate function. take a look on these articles for better understanding,

http://www.salesforce.com/us/developer/docs/apexcode/Content/langCon_apex_SOQL_agg_fns.htm

http://www.salesforce.com/us/developer/docs/soql_sosl/Content/sforce_api_calls_soql_select_groupby.htm

so, restructuring your query it will look like this,

[SELECT Profile__c,name,count(NumberofLogin__c),Startdate__c,EndDate__c FROM User__c where GROUP BY PROFILE__c, name, startdate__c, enddate__c];

1

It is quite common when aggregating to only query for two values such as:

SELECT Profile__c, SUM(NumberOfLogin__c)
FROM User__c
WHERE ????
GROUP BY PROFILE__c

so that the result is simply the "total number of logins per profile" assuming your User__c.NumberOfLogin__c is already the number of logins for that user. Having several terms in the "GROUP BY" typically breaks the data up into more groups than is convenient.

You can sometimes get additional useful information in the one query by wrapping multiple selected fields in an aggregating function like this:

SELECT Profile__c, MIN(Startdate__c), MAX(EndDate__c), COUNT(Id), SUM(NumberOfLogin__c)
FROM User__c
WHERE ????
GROUP BY PROFILE__c

providing the earliest start date, the latest end date and the number of users per profile at the same time as the total logins.

The bottom line is that all the fields in the "SELECT" term must either be wrapped in an aggregate function or included in the "GROUP BY" list. But as I said at the beginning of this post keeping things simple is often the best approach.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.