error thrown while trying to do a group by

Question

I'm trying to write a query that gives me the name of users and the total number of ids assigned to them.

Here I've Daily_Task__c a custom object with a lot of fields, here I'm trying to get the total number of cases assigned to a user and I'm trying the below query

SELECT count(id), User_Name__c FROM Daily_Task__c group by User_Name__c 

when I'm running this query I get the error as below.

[object Object]: User_Name__c FROM Daily_Task__c group by User_Name__c ^ ERROR at Row:1:Column:58 field 'User_Name__c' can not be grouped in a query call

but when I do the below query it is working fine

SELECT id, User_Name__c FROM Daily_Task__c order by User_Name__c 

it is showing the list of all the ids and users(sorted).

please let me know on where am I going wrong with the group by case and how can I fix it.

Here is what I see when I run System.debug(Daily_Task__c.User_Name__c.getDescribe()); in my developer console.

00:32:47:018 USER_DEBUG [1]|DEBUG|Schema.DescribeFieldResult[getByteLength=3900;getCalculatedFormula=Daily_Update__r.Ass_Usr___r.First_Name__c;getCompoundFieldName=null;getController=null;getDefaultValue=null;getDefaultValueFormula=null;getDigits=0;getFilteredLookupInfo=null;getInlineHelpText=null;getLabel=DE Name;getLength=1300;getLocalName=User_Name__c;getMask=null;getMaskType=null;getName=User_Name__c;getPrecision=0;getReferenceTargetField=null;getRelationshipName=null;getRelationshipOrder=null;getScale=0;getSoapType=STRING;getSobjectField=User_Name__c;getType=STRING;isAccessible=true;isAggregatable=true;isAiPredictionField=false;isAutoNumber=false;isCalculated=true;isCascadeDelete=false;isCaseSensitive=false;isCreateable=false;isCustom=true;isDefaultedOnCreate=false;isDependentPicklist=false;isDeprecatedAndHidden=false;isDisplayLocationInDecimal=false;isEncrypted=false;isExternalId=false;isFilterable=true;isFormulaTreatNullNumberAsZero=true;isGroupable=false;isHighScaleNumber=false;isHtmlFormatted=false;isIdLookup=false;isNameField=false;isNamePointing=false;isNillable=true;isPermissionable=true;isQueryByDistance=false;isRestrictedDelete=false;isSearchPrefilterable=false;isSortable=true;isUnique=false;isUpdateable=false;isWriteRequiresMasterRead=false;]

Thanks

Answer 1

The describe information you were able to extract shows

isGroupable=false

which is a proximate, if not very useful, answer. In this case, the real problem blocking you from using this field in a GROUP BY appears to be that it is a cross-object formula field:

Daily_Update__r.Ass_Usr___r.First_Name__c

The documentation on GROUP BY Considerations does not make clear exactly which field types inherently are and are not groupable, but it makes some amount of naive sense to me that cross-object formula fields wouldn't be groupable - there'd certainly be a performance cost involved, at minimum.

As a simple illustration that this is the cause, I created a custom formula field Account_Name__c on Contact, with the formula Account.Name. Having done, so the query

SELECT count(Id) FROM Contact GROUP BY Account_Name__c

returns the same error you're seeing, despite that

SELECT count(Id) FROM Contact GROUP BY Account.Name

is perfectly legal. Since Account.Name is not encrypted, long text, rich text, or anything else fancy, it looks like the mere fact of being a cross-object formula makes the field non-groupable.

Edit: I've discovered that the conclusion above is incorrect, as detailed in another answer. It is not the cross-object nature of the formula field, but its type, that determines groupability. String formula fields are not groupable.