You need to sign in to do that
Don't have an account?
Problem in Aggregate function
Hello..
There is problem in using Aggregate function, but i dont know whats wrong in this please help me..
But it save with out any error
public class FBR_InquiryMonthly{
@future public static void ValAdd(Set<Id> InquiryId) { integer total;
string accountname;
List<FBR_Inquiry__c> Inquiry= [SELECT KPIInquiryContent__c,KPIInquiryContent__r.Account__c,CommodityName__c,ModifyDate__c,Status__c from FBR_Inquiry__c where Id =:InquiryId];
FBR_Inquiry__c FBR= Inquiry[0]; Date startDate=FBR.ModifyDate__c.toStartOfMonth();
Date endDate=FBR.ModifyDate__c.toStartOfMonth().addMonths(1);
LIST<AggregateResult> countmonth=[SELECT count(KPIInquiryContent__c) coun from FBR_Inquiry__c where KPIInquiryContent__r.Account__c=:FBR.KPIInquiryContent__r.Account__c and ModifyDate__c >= :startDate AND ModifyDate__c < :endDate and PayoffObject__c=:'○' and Status__c IN('hgj' ,'fyh')];
List<KPIAccident__c> acc= [SELECT Date__c,MonthlyGeneratedCount__c,MonthlyPPM__c from KPIAccident__c where Date__c >= :startDate and Date__c < :endDate and Account_Name__c=:FBR.KPIInquiryContent__r.Account__c];
LIST<AggregateResult> calmonth=[Select count(ImportCount__c) icount from WorkResultsMonth__c where WorkDate__c >= :startDate and WorkDate__c < :endDate ];
if(!acc.isEmpty() ) {
KPIAccident__c accupdate = acc[0];
decimal decimalcountmonth = 0 ;
string strcount= '' +countmonth[0].get('coun') ;
decimalcountmonth=decimal.ValueOf(strcount);
accupdate.MonthlyGeneratedCount__c=decimalcountmonth;
decimal totalmonth = 0 ; string strcounts= '' +calmonth[0].get('icount') ;
totalmonth=decimal.ValueOf(strcounts);
accupdate.MonthlyPPM__c=((decimalcountmonth/totalmonth)*1000000);
update accupdate;
}else {
KPIAccident__c kpi = new KPIAccident__c();
List<KPIAccident__c> ToUpdate = new List<KPIAccident__c>();
kpi.Date__c=FBR.ModifyDate__c;
kpi.Account__c=FBR.KPIInquiryContent__r.Account__c;
kpi.itemname__c= FBR.CommodityName__c;
decimal decimalcountmonths = 0 ; string strcoun= '' +countmonth[0].get('coun') ; decimalcountmonths=decimal.ValueOf(strcoun);
kpi.MonthlyGeneratedCount__c=decimalcountmonths;
decimal totalmonths = 0 ; string strcountss= '' +calmonth[0].get('icount') ;
totalmonths=decimal.ValueOf(strcountss);
kpi.MonthlyPPM__c=((decimalcountmonths/totalmonths)*1000000);
ToUpdate.add(kpi);
insert ToUpdate;
} } }
Your aggregate query does not include a GROUP BY clause. Now, if you were trying to get the plain result of the COUNT(), you could define your result variable as an Integer - but if you wanted to get a COUNT by, say, WorkResultsMonth__c, then you want to append "GROUP BY WorkResultsMonth__c" to your query.
Good luck!
Thanks for ur reply..i used groupby but still there is no change...
i dont think so that you required a aggregate object to catch the results ...i think you can make you some thins like this
integer calmonth=[Select count(ImportCount__c) icount from WorkResultsMonth__c where WorkDate__c >= :startDate and WorkDate__c < :endDate ];
if it is not null then you have some records in the database...
Return type of Aggregate function is always list of Aggreated resutls ( Unless its count of records). Could you please explain us
why you're converting the count(ImportCount__c) into String and then back to Decimal ( Without any manipulation)?
Hi Kiran,
If i use that i got error such like:
I Changed My Code but now i am getting Error Message as
public class FBR_InquiryMonthly{
@future public static void ValAdd(Set InquiryId) {
integer total; List<FBR_Inquiry__c> Inquiry= [SELECT KPIInquiryContent__c,KPIInquiryContent__r.Account__c,CommodityName__c,ModifyDate__c,Status__c from FBR_Inquiry__c where Id =:InquiryId]; for(FBR_Inquiry__c FBR: Inquiry)
{ Date startDate=FBR.ModifyDate__c.toStartOfMonth();
Date endDate=FBR.ModifyDate__c.toStartOfMonth().addMonths(1);
List<KPIAccident__c> acc= [SELECT Date__c,MonthlyGeneratedCount__c,MonthlyPPM__c from KPIAccident__c where Date__c >= :startDate and Date__c < :endDate and Account_Name__c=:FBR.KPIInquiryContent__r.Account__c];
for(KPIAccident__c accupdate : acc)
{
if(!acc.isEmpty() )
{
integer countmonth=[SELECT count(KPIInquiryContent__c) coun from FBR_Inquiry__c where ModifyDate__c >= :startDate AND ModifyDate__c < :endDate and PayoffObject__c=:'○' and Status__c IN('rhddf' ,'rtsu')];
accupdate.MonthlyGeneratedCount__c=Integer.Valueof(countmonth.get('coun'));
integer calmonth=[Select count(ImportCount__c) icount from WorkResultsMonth__c where WorkDate__c >= :startDate and WorkDate__c < :endDate ]; integer totalmonth=Integer.Valueof(calmonth.get('icount'));
accupdate.MonthlyPPM__c=((countmonth/totalmonth)*1000000);
update accupdate;
}else {
KPIAccident__c kpi = new KPIAccident__c();
List<KPIAccident__c> ToUpdate = new List<KPIAccident__c>();
kpi.Date__c=FBR.ModifyDate__c;
kpi.Account__c=FBR.KPIInquiryContent__r.Account__c;
kpi.itemname__c= FBR.CommodityName__c;
Integer countmonths=[SELECT count(KPIInquiryContent__c) couns from FBR_Inquiry__c where KPIInquiryContent__r.Account__c=:FBR.KPIInquiryContent__r.Account__c and ModifyDate__c >= :startDate AND ModifyDate__c < :endDate and PayoffObject__c=:'○' and Status__c IN('sdgs' ,'szghd')]; kpi.MonthlyGeneratedCount__c=Integer.Valueof(countmonths.get('couns'));
integer calmonths=[Select count(ImportCount__c) icounts from WorkResultsMonth__c where WorkDate__c >= :startDate and WorkDate__c < :endDate ];
integer totalmonths=Integer.Valueof(calmonths.get('icounts'));
kpi.MonthlyPPM__c=((countmonths/totalmonths)*1000000);
ToUpdate.add(kpi); insert ToUpdate;
} } }}}
Could you state the original problem?
Repeating basically what you have works fine in dev console.