SOQL Group By error: duplicate alias:name

Question

So I have the following SOQL Query:

SELECT Id, Name, Week_Name__c, Contact__c, Contact__r.Name, SUM(Hours_Worked_Rollup__c) hours, Status__c, Unique_Id__c, Week_Start_Date__c, Approver__c, Approver__r.Name, Project_Name__c, Contact_Name__c, Status_Indicator__c, Project_Name_Clone__c, EmployeeId__c, Project_Contact__c, ProjectId__c, Project_Contact__r.Project__r.Client__r.Name, Project_Contact__r.Project__r.Project_Name__c FROM Time_Sheet__c GROUP BY Contact__c.Name

when I try to run it I get an error that says "duplicate alias: name",

does anybody know why I'm getting this error ? It only happens when I use "Group By".

Answer 1

The text of the error is not particularly clear, but the problem is that you're making an aggregate query while trying to select fields that you have not grouped or aggregated. You cannot do that in an aggregate query, because you'll get back one row per grouped value (Contact__r.Name) and you haven't specified how to store the potentially multiple values are present in the underlying rows for fields like ProjectId__c.

Aggregate queries return AggregateResult objects, not sObjects. AggregateResults don't have nested object data like sObjects do, just named properties. Here, you are trying to select multiple fields across relationships whose API names are Name, which would collide with one another if you tried to store them in the same AggregateResult. That's why you get a rather inscrutable error message.

But fundamentally, you just cannot structure this query in this way.

To query for hours by employee, you'd have to do something like this:

SELECT Contact__c, SUM(Hours_Worked_Rollup__c) hours
FROM Time_Sheet__c 
GROUP BY Contact__c

Then your AggregateResult records will have two fields populated: Contact__c, the Id, and the hours rollup value. You can then take your Contact Ids and make a second, non-aggregated query to pull all of the other details that you require.

If you're trying to display all this data together on the front end, you'll often need to define a wrapper class to store the aggregate data along with the contact details.