[SalesForce] getting group by aggregate errors in this query

I need to count the total number of orgs. Why won't this query work? I get errors not matter what I group or not group. I only put in Group by to try to placate the compiler but he's in a very bad mood today. Without COUNT(ORG__C) and the group by clause it works.

Select BOG_Membership_Type_New__c,Contact_Name__c, ORG__c, COUNT(ORG__C)
FROM BOG_Membership__c 
WHERE BOG_Membership_Status__c = 'Active' 
AND BOG_Membership_Type_New__c <> ''
GROUP BY BOG_Membership_Type_New__c,Contact_Name__c, ORG__c
ORDER BY BOG_Membership_Type_New__c ,Contact_Name__c, ORG__c

Best Answer

The rule of thumb here is

Any field thats included in Aggregate SOQL has to be either aggregated or grouped

When using aggregate functions like count, all other fields must be grouped.

The final query will be

Select BOG_Membership_Type_New__c, Contact_Name__c,COUNT(ORG__C)
 FROM BOG_Membership__c 
 WHERE BOG_Membership_Status__c = 'Active' 
 AND BOG_Membership_Type_New__c <> ''
 GROUP BY BOG_Membership_Type_New__c, Contact_Name__c
 ORDER BY BOG_Membership_Type_New__c , Contact_Name__c

Related Solutions

[SalesForce] Problem with EntitySubscription query and getting a Non-selective query exception

Per our chat over twitter and a related tweet from Andrew Waite I think the fix here is to remove with sharing from your class that contains the query.

While the error messaging could be improved the docs on EntitySubscription repeatedly hint that under the hood the platform has a hard time processing any reasonably large queries on this table when sharing rules have to be calculated due to the massive number of possible joins required (it's going to join to the table parentId belongs to, THEN to that record's sharing table). By running without sharing all the extra joins to enforce sharing wouldn't be needed and your query then looks much more "reasonable" to the database.

You can always use a subsequent query to UserRecordAccess to validate that the running user should be able to see all the EntitySubscriptions you fond when running without sharing if you still need to enforce security.

[SalesForce] Reference fields on parent object in aggregate query

What happens if you alias the field, does that work?

AggregateResult[] groupedResults = [
  SELECT Account.Type type, count(Id) oppCount
  FROM Opportunity 
  GROUP BY Account.Type];

// snip

System.Debug(ar.Get('type'));

Notice that if you are doing an aggregation (GROUP BY) you can create an alias for both the summarized field and for other fields. This is not documented, but is the only way to get this to work. In your GROUP BY field you need to use the full name of the field, but when getting info from the AggregateResults you use the alias.

Best Answer

Related Solutions

[SalesForce] Problem with EntitySubscription query and getting a Non-selective query exception

[SalesForce] Reference fields on parent object in aggregate query

Related Topic