+ Start a Discussion

Aggregate query with parent and child relationship


I need to wriate a SOQL query to get the aggregate value of Contacts object custom objects.
I have created two custom fields ( NoOfPagesPrinted__c, DatePrinted__c) in contact object.
I need to get sum of pages printed of each account. I have written below query. Please correct this query.

Select Account.ID, Account.Name, SUM(Select Contact.NoOfPagesPrinted__c from contact where Contact.DatePrinted__c = LAST_N_DAYS:30) from Account where Account.AccountType__c ='LES' group by Account.Name


Gonzalo AbrunaGonzalo Abruna
You can do it in two steps, first the query, and then a size() method:

//Do the soql query:
List<Account> lAccounts = [Select ID, Name, (Select NoOfPagesPrinted__c from Contacts where DatePrinted__c = LAST_N_DAYS:30) from Account where AccountType__c ='LES'];

//Get the number of contacts per each account:
for(Account a : lAccounts)
      Integer numberOfContacts = a.Contacts.size();

Hi Gonzalo Abruna,

Thanks for your response. Actually i am writing a batch class to calculate last 30 days sum of pages printed by each contact of an account.

Account Field: Last30DaysSum__c.
Contact Fields: DatePrinted__c, NoOfPagesPrinted__c.

I need to calculate the sum of pages printed in 30 last days of each account and update the value Last30DaysSum__c.
So i am writing a batch class with interable interface.

I have tried with child to parent relationship with aggregate query

       Select Contact.Account.Name, sum(Contact.NoOfPagesPrinted__c) From contact where Contact.DatePrinted__c = LAST_N_DAYS:30 and  contact.Account.AccountType__c ='LES' group by contact.Account.Name.

But i cant update Account.Last30Days__c field, because the above query doesnot returning the account id, so update is not working.
If i try with parent to child relationship query i am not getting the sum of pages printed of each account.


global class IterableAggregator implements Database.Batchable<AggregateResult> {
    global Iterable<AggregateResult> start(Database.batchableContext info){
        return new AggregateResultIterable();
    global void execute(Database.BatchableContext BC, List<Sobject> scope){
         System.debug('Scope:'+ scope);
         List<Account> oldAcc = new List<Account>();
         for(Sobject ar : scope)
             Account acc = new Account();
             System.debug('Name:'+ ar.get('Name'));
             System.debug('Avg:' + integer.valueof(ar.get('expr0')));
             a.Last30Days_sum__c= integer.valueof(ar.get('expr0')); // Updating
        update oldAcc ;
    global void finish(Database.BatchableContext BC){
    global class AggregateResultIterable implements Iterable<AggregateResult> {
        global Iterator<AggregateResult> Iterator(){
            return new AggregateResultIterator();
    global class AggregateResultIterator implements Iterator<AggregateResult> {
        AggregateResult [] results {get;set;}
        Integer index {get; set;}
        global AggregateResultIterator() {
            index = 0;
            string acType='LES';
            String query = 'Select ID, Name, (Select NoOfPagesPrinted__c from Contacts where DatePrinted__c = LAST_N_DAYS:30)
                                 from Account where    AccountType__c =:acType';

            //Lets query and collect the result in list of accounts.
            List<Account> accList=Database.query(query);

            for(Account ac:accList){
               system.debug('AccList:'+ ac.contact.NoOfPagesPrinted__c ); // Here i need to get the sum of pages printed by all contacts.
            //results = Database.query(query);           
        global boolean hasNext(){
           return results != null && !results.isEmpty() && index < results.size();
        global AggregateResult next(){
            return results[index++];