4

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
3
  • 1
    Can you add some of the errors you are receiving?
    – zainogj
    Mar 20, 2016 at 13:31
  • Group field should not be aggregated ORG__c. When I remove it from the Group By it tells me that it needs to be aggregated !?!
    – Apex N-u-b
    Mar 20, 2016 at 13:37
  • You can not select fields that are not aggregated in the group by clause Mar 20, 2016 at 13:54

3 Answers 3

6

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
4
  • I get Contact_Name__c can not be grouped in a query call
    – Apex N-u-b
    Mar 20, 2016 at 14:14
  • whats the data type of Contact_Name__c ? Is it a formula field ? Mar 20, 2016 at 14:15
  • Yes, a text formula
    – Apex N-u-b
    Mar 20, 2016 at 14:19
  • 2
    You cannot use formula field to GROUP BY .In past I have done workflows to replace formula fields to use in Aggregate SOQL. Mar 20, 2016 at 14:21
1

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.

1

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

You must log in to answer this question.

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