Why am I getting group by aggregate errors in this query

Question

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

Answer 1

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

Answer 2

Luckily, having read your other question I know that Contact_Name__c need not be a formula field. Include it in your aggregate query like so:

SELECT
    COUNT(Id),
    BOG_Membership_Type_New__c,
    Contact__r.Name,
    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__r.Name,
    Org__c
ORDER BY
    BOG_Membership_Type_New__c,
    Contact__r.Name,
    Org__c

When you are grouping by one or more fields, you are asking for all the permutations of those fields. To meaningfully display the different permutations, the select must contain all the fields you are grouping by.

Imagine you have 3 contacts, Alan Anderson, Alan Bradley and Charles Cooper. Next, you select just the first name whilst grouping by first name and surname. This gives you 3 rows:

• Alan (Anderson)
• Alan (Bradley)
• Charles (Cooper)

There are 2 Alans, but the result set is ambiguous because it doesn't tell you the all-important surname field to distinguish the two Alans.

Answer 3

At present GROUP BY clause cannot include many field types including calculated, number and currency fields. Vote here to get this changed https://success.salesforce.com/ideaView?id=08730000000knecAAA