You need to sign in to do that
Don't have an account?

Aggregate query with parent and child relationship
Hi,
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
-Thanks.
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
-Thanks.
//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
oldAcc.add(acc);
}
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++];
}
}
}
-------------------------------------------------------------------------------------------------------