[SalesForce] How to get a grouping count in a SOQL query

I'm trying to figure out the best way to get a count of the number of kids per location.

Here is the current query that works and populates my VF page.

SELECT puLocation__r.Name LocationName, 
                    puLocation__r.notes__c LocationNotes,
                    puLocation__r.Milk_Location__c MilkLocation,
                    puLocation__r.Location_type__c LocationType
             FROM Child_Consent_Form__c 
             GROUP BY puLocation__r.Name, 
                      puLocation__r.notes__c,
                      puLocation__r.Milk_Location__c,
                      puLocation__r.Location_type__c];

This query below worked in the query editor but the forum helped me figure out that this wasn't going to work on my VisualForce page because of the count(). The count(Name) gave me the total number of kids per location.

//Records = [SELECT puLocation__r.Name, puLocation__r.Milk_Location__c, puLocation__r.notes__c, COUNT(Name) from Child_Consent_Form__c GROUP BY puLocation__r.Name, puLocation__r.Milk_Location__c, puLocation__r.notes__c ]; 

Here is the original thread.

Unknown property 'SObject. – Why can't I display the object?

My problem is that I can't figure out how to get around this.

Best Answer

Your question is very clearly answered in the documentation on Aggregate Functions:

COUNT() and COUNT(fieldName)

Returns the number of rows matching the query criteria. For example using COUNT():

SELECT COUNT()
FROM Account
WHERE Name LIKE 'a%'

For example using COUNT(fieldName):

SELECT COUNT(Id)
FROM Account
WHERE Name LIKE 'a%'

The COUNT(fieldName) syntax is available in API version 18.0 and later. If you are using a GROUP BY clause, use COUNT(fieldName) instead of COUNT(). For more information, see COUNT() and COUNT(fieldName).

Note that last paragraph. Specifically:

If you are using a GROUP BY clause, use COUNT(fieldName) instead of COUNT().

That means you would add COUNT(Id) as your field, and optionally include an alias:

SELECT Parent__r.Name parentName, COUNT(Id) childCount
FROM MyObject__c GROUP BY Parent__r.Name
Related Topic