+ Start a Discussion
duke1duke1 

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);

}

Best Answer chosen by Admin (Salesforce Developers) 
Going South.ax738Going South.ax738

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

Going South.ax738Going South.ax738

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

 

duke1duke1

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.

Going South.ax738Going South.ax738

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.

This was selected as the best answer
duke1duke1

Thanks, dealer__r worked.