You need to sign in to do that
Don't have an account?
David 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
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
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
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.
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.
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.