You need to sign in to do that
Don't have an account?
sk_63
need help in aggregate query
I have a custom apex page where the records will be displayed every month for a logged in user. They are as follows:
Now, my requirement is to display only max records of an Account instead of all the records. It should be as follows:
Below is my controller:
Now, my requirement is to display only max records of an Account instead of all the records. It should be as follows:
Below is my controller:
public class PD_ClosedAccounts { public String accId {get;set;} public String accId2 {get;set;} public list<Custom_Month__c> accList2 {get; set;} public List<Custom_Month__c> CustomMonthList {get;set;} public list<string> dID = new list<string>(); public map<Id,string> CusAccID = new map<Id,string>(); public List<User> usr = new List<User>(); public PD_ClosedAccounts(){ // Get the logged in user's Account Id usr = [Select Id, PD_Acc__c From User Where Id=:UserInfo.getUserId()]; if(usr != null ) { accId = usr[0].PD_Acc__c; Set<Id> allCusAccIDs = new Set<Id>(); allCusAccIDs.add(accId); // Get all the associated Acc records of the logged in user: for(Account associatedAcc: [Select ID, Cus_Acc_ID From Account Where Id = :accId] ){ CusAccID.put(associatedAcc.ID, associatedAcc.Cus_Acc_ID); } dID=CusAccID.values(); CustomMonthList = [SELECT Name, Acc__c, Acc__r.Name, Acc__r.Acc_Id_Format__c, Acc__r.Level__c, Acc__r.Location_Country__c, Processing_Month__c, Acc_c__c FROM CustomMonthList WHERE Acc_c__c != 0 AND Processing_Date__c >= LAST_MONTH AND Acc__r.Spn_Id__c IN :dID order by Acc__r.Acc_Id_Format__c DESC]; } AggregateResult[] groupedResults = [SELECT id id, MAX(Acc_c__c) maxAccountC FROM CustomMonthList WHERE ID IN:CustomMonthList group by Processing_Date__c, Acc__r.Acc_Id_Format__c]; List<Id> theIds = new List<Id>(); for (AggregateResult result : groupedResults) { theIds.add((Id) result.get('id')); } accList2 = [SELECT Acc__c, Acc__r.Acc_Id_Format__c FROM Custom_Month__c WHERE ID IN :theIds]; if(accList2!= Null){ for(integer i=0;i<accList2.size();i++){ accId2 = accList2[i].Acc__c; } } } }
From the above controller, even though I have aggreagte query fetching max of Account_c__c field from CustomMonthList, I am getting all the 4 records in my page instead of Max ones.
Can anyone suggest the changes in my code so that I can able to fetch only max records?
Thank you in advance
SK
Replace by this code:
AggregateResult[] groupedResults = [SELECT Id,
MAX(Acc_c__c),
maxAccountC
FROM CustomMonthList
WHERE ID IN:CustomMonthList
group by Processing_Date__c,
Acc__r.Acc_Id_Format__c];
OR
Follow this code:
AggregateResult[] groupedResults = [SELECT Id,
Acc_c__c,
maxAccountC
FROM CustomMonthList
WHERE ID IN:CustomMonthList
group by Processing_Date__c,
Acc__r.Acc_Id_Format__c
ORDER BY Acc_c__c DESC];
I hope you find the above solution helpful. If it does, please mark as Best Answer to help others too.
Thanks and Regards,
Ajay Dubedi
www.ajaydubedi.com
I tried your suggested code, but that didn't work for me.
I'm getting the following error:
Can you please suggest any modifications.
Thanks,
SK
Adding to my above comment,
My requirement is to display only maximum records from each account. As per your given query, I will be displaying max records on the top. I'm afraid, that will not suffice my requirement