+ Start a Discussion
David FryDavid Fry 

Help building SOQL Aggregate Query with Relationship

Hello All, I come from a SQL data background and I'm trying to build a very simple aggregate query joining to related objects.  No matter what I do, I get an error. Is there any way to build this simple query?  This is not to be implemented within APEX code, so doing it in pieces or using variables isn't an option.  I need a direct query as this is for use in organizing and improving some of our internal data collection and integration tools.

Here are the details:
Parent Object: Trunk_Group__c
Child Related Object: Trunk_Telephone_Lines__c

Here are the attempts I have made to no avail (I've tried other variations as well but no go):

SELECT Lead_TN__c, (Select Count(Id) From Trunk_Telephone_Lines__r ) FROM Trunk_Group__c Where Related_Trunk_Service_Request__c = 'big_long_sfdc_id' Group By Lead_TN__c


SELECT Trunk_Group__r.Lead_TN__c, Count(Id) FROM Trunk_Telephone_Lines__c Where  Trunk_Group__r.Related_Trunk_Service_Request__c  = 'big_long_sfdc_id'
Group By Trunk_Group__r.Lead_TN__c
Best Answer chosen by David Fry
AshwaniAshwani
Hi David Fry,

As I can understand SOQL queries:-

For this Query:
SELECT Lead_TN__c, (Select Count(Id) From Trunk_Telephone_Lines__r ) FROM Trunk_Group__c Where Related_Trunk_Service_Request__c = 'big_long_sfdc_id' Group By Lead_TN__c

In this query you are using group by clause in inner query. Which is not supported.


I get this error in WorkBench:
INVALID_FIELD:
SELECT Trunk_Group__r.Lead_TN__c, Count(Id)

This issue is related to object relationship name. Here Trunk_Group__r is reffered to relationship name.
relationship name can be different from an object api name so you should check in object lookup field details about the relationship name.


All Answers

KevinPKevinP
David, it'd be helpful if you could share the errors you're recieving as well.

I suspect, that you need to provide all the elements within the group by clause. Something like this:

SELECT Trunk_Group__r.Lead_TN__c, Count(Id) NiceAliasHere
FROM Trunk_Telephone_Lines__c
WHERE  Trunk_Group__r.Related_Trunk_Service_Request__c  = 'big_long_sfdc_id'
Group By Trunk_Group__r.Lead_TN__c, NiceAliasHere

But I'll be able to help more when you share the error your getting.
David FryDavid Fry
You typically do not add the "aggregate" column into the "Group By" Clause, but here are the errors...

For this Query:
SELECT Lead_TN__c, (Select Count(Id) From Trunk_Telephone_Lines__r ) FROM Trunk_Group__c Where Related_Trunk_Service_Request__c = 'big_long_sfdc_id' Group By Lead_TN__c

I get this error in WorkBench:
MALFORMED_QUERY: only root queries support aggregate expressions


For this Query:
SELECT Trunk_Group__r.Lead_TN__c, Count(Id) FROM Trunk_Telephone_Lines__c Where  Trunk_Group__r.Related_Trunk_Service_Request__c  = 'big_long_sfdc_id'
Group By Trunk_Group__r.Lead_TN__c

I get this error in WorkBench:
INVALID_FIELD:
SELECT Trunk_Group__r.Lead_TN__c, Count(Id)
^
ERROR at Row:1:Column:8
Didn't understand relationship 'Trunk_Group__r' in field path. If you are attempting to use a custom relationship, be sure to append the '__r' after the custom relationship name. Please reference your WSDL or the describe call for the appropriate names.


AshwaniAshwani
Hi David Fry,

As I can understand SOQL queries:-

For this Query:
SELECT Lead_TN__c, (Select Count(Id) From Trunk_Telephone_Lines__r ) FROM Trunk_Group__c Where Related_Trunk_Service_Request__c = 'big_long_sfdc_id' Group By Lead_TN__c

In this query you are using group by clause in inner query. Which is not supported.


I get this error in WorkBench:
INVALID_FIELD:
SELECT Trunk_Group__r.Lead_TN__c, Count(Id)

This issue is related to object relationship name. Here Trunk_Group__r is reffered to relationship name.
relationship name can be different from an object api name so you should check in object lookup field details about the relationship name.


This was selected as the best answer
David FryDavid Fry
Thank you, that was perfect. I did not know that relationship names are based on the lookup field and not the master object.  I appreciate the help!