+ Start a Discussion
ChermaduraiChermadurai 

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;

} }  }

SFFSFF

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!

ChermaduraiChermadurai

Thanks for ur reply..i used groupby but still there is no change...

 

kiranmutturukiranmutturu

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...

Gunners_23Gunners_23

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

ChermaduraiChermadurai

Hi Kiran,

 

If i use that i got error such like:

Error: Compile Error: Illegal assignment from LIST<AggregateResult> to Integer at line 11 column 1
ChermaduraiChermadurai

I Changed My Code but now i am getting Error Message as

 

Error: Compile Error: Illegal assignment from LIST<AggregateResult> to Integer at line 18 column 11

 

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;

} } }}}

Starz26Starz26

Could you state the original problem?

 

Repeating basically what you have works fine in dev console.