You need to sign in to do that
Don't have an account?
Aggregate functions with subqueries
From what I can tell from error messages, you are not allowed to use aggregate functions in subqueries. Because of this limitation, I have rearranged my query so the aggregate functions are in the main query, but I am getting errors on the relationships. Can someone help me sort this out?
I have renamed Opportunity as Dealer (plural: Dealers).
I have a Master-Detail relationship between Dealer (master) and custom sObject Car (plural is Cars), where there are many Car sObjects to one Dealer, and many Dealers to one Account.
I am trying to use the aggregate functions to sum up certain fields in the Car sObject for each Dealer. For example, sum all the Car_Number_Field_1__c fields for all the Cars associated with one Dealer; then elsewhere I will roll up all these Dealer sums for each Account.
There are ways to do this without aggregate functions, but I am interested in getting this to work using aggregate functions. The following select statement in the for statement will not compile. I get errors in the subquery where the complier does not understand the relationship with Dealers__r and it doesn't understand Dealer__c. Can someone help me figure out how to get this to compile? Thanks.
I have created a Set of relevant Accounts in accountIDs
Map<Id, List<Decimal>> carTotals = new Map<Id, List<Decimal>>();
for (AggregateResult aggregateData : [select Dealer__c,
(select Id, AccountID from Dealers__r where Id = Dealer__c),
SUM(Car_Number_Field_1__c) Car1Total, SUM(Car_Number_Field_2__c) Car2Total,
SUM(Car_Number_Field_3__c) Car3Total from Car__c
where AccountID in :accountIds Group by Dealer__c]) {
List<Decimal> carSums = new List<Decimal>{(Decimal)aggregateData.Get('Car1Total'),
(Decimal)aggregateData.Get('Car2Total'), (Decimal)aggregateData.Get('Car3Total')};
carTotals.put(AccountID, carSums);
}
On object definition, what is the fkey column name that you in Cars__c that refer to dealers object?
is it dealer__c or dealers__c?
If its dealer__c use dealer__r instead of dealers__r.
All Answers
For any group by with aggregates, on the group by clause, you should include all columns that are not aggregates. Here in your query, your second parameter has 2 columns that does not make it to group by.
Try the following.
select Dealer__c, Dealers__r.AccountID,
SUM(Car_Number_Field_1__c) Car1Total, SUM(Car_Number_Field_2__c) Car2Total,
SUM(Car_Number_Field_3__c) Car3Total from Car__c
where AccountID in :accountIds Group by Dealer__c, Dealers__r.AccountID
Thanks for your help. I still get the following compiler error:
Error: Compile Error: Didn't understand relationship 'Dealers__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. at line 13 column 42
This is the Dealers__r.AccountID that it doesn't understand.
On object definition, what is the fkey column name that you in Cars__c that refer to dealers object?
is it dealer__c or dealers__c?
If its dealer__c use dealer__r instead of dealers__r.
Thanks, dealer__r worked.