+ Start a Discussion
prasanth puvvada 4prasanth puvvada 4 

Soql avg,sum,max error in apex program.

I am wrting sql query for getting count,avg,sum,min,max aggregate functions at apex class. but i am getting this error.  please help.
 
Error: Compile Error: Illegal assignment from List<AggregateResult> to Integer at line 12 column 1  


public class soqlfunc
{
public integer count12{set;get;}
public integer avg{set;get;}
public integer sum{set;get;}
public integer max{set;get;}
public integer min{set;get;}

public soqlfunc()
{
count12=[select count() from transaction__c ];
avg=[select avg(amount__c) from transaction__c];
sum=[select sum(amount__c) from transaction__c   ];
max=[select max(amount__c) from transaction__c   ];
min=[select min(amount__c) from transaction__c   ];
}
}

 
sandeep sankhlasandeep sankhla
Hi Prasanth,

please use as below:

for(AggregateResult objAgg : [ select   sum(amount__c), max(amount__c) from transaction__c ])
    {
        
          count12 = (Integer)objAgg.get('expr0');
          max = (Integer)objAgg.get('expr1');
        
    }

Please use as I mentioned above....you are getting error because you cant assign AggregateResult  into integer variable..

Tyoe casting is needed..

Thanks,
Sandeep
surasura
use below code 
AggregateResult[] groupedResults
  = [SELECT AVG(Amount__c) aver,sum(Amount__c) sum,max(Amount__c) max,min(Amount__c) min FROM transaction__c];
  
double avgAmount = double.valueOf(groupedResults[0].get('aver'));
double sumAmount = double.valueOf(groupedResults[0].get('sum'));  
double maxAmount = double.valueOf(groupedResults[0].get('max'));  
double minAmount = double.valueOf(groupedResults[0].get('max'));
Amit Chaudhary 8Amit Chaudhary 8
Please try below code :-
public class soqlfunc
{
public integer count12{set;get;}
public double  avg{set;get;}
public double  sum{set;get;}
public double  max{set;get;}
public double  min{set;get;}

	public soqlfunc()
	{
		count12 = [SELECT count()  FROM transaction__c ];
		AggregateResult[] groupedResults = [SELECT AVG(Amount__c) aver,sum(Amount__c) sum,max(Amount__c) max,min(Amount__c) min FROM transaction__c];

		avg = double.valueOf(groupedResults[0].get('aver'));
		sum = double.valueOf(groupedResults[0].get('sum'));  
		max = double.valueOf(groupedResults[0].get('max'));  
		min = double.valueOf(groupedResults[0].get('max'));

	}

}

Some more example for you
AggregateResult[] groupedResults
  = [SELECT CampaignId, AVG(Amount)
      FROM Opportunity
      GROUP BY CampaignId];

for (AggregateResult ar : groupedResults)  
{
    System.debug('Campaign ID' + ar.get('CampaignId'));
    System.debug('Average amount' + ar.get('expr0'));
}
Please check below post for more details:-
http://blog.jeffdouglas.com/2010/04/12/using-aggregateresult-in-salesforce-com-soql/
https://developer.salesforce.com/docs/atlas.en-us.soql_sosl.meta/soql_sosl/sforce_api_calls_soql_select_agg_functions.htm

Please let us know if this will help you



 
prasanth puvvada 4prasanth puvvada 4
thankyou amith.  For just testing i add this code           system.debug('*****************************'+min);

and tested in anoymous window. why the debug line not visibe ?
prasanth puvvada 4prasanth puvvada 4
dear please tell me that,  i am executing this query at wrokbench.developerforce.com/query.php  then it is giving one answer and same query is giving another from VF - APEX page.   

query is:- SELECT sum(price__c) sum FROM Inventory__c

workbench answer:- 5.8090315656724E+15

VF page answer:- 143733.0  

waiting for your help. 
 
Amit Chaudhary 8Amit Chaudhary 8
Please add debug like below code :-
public class soqlfunc
{
public integer count12{set;get;}
public double  avg{set;get;}
public double  sum{set;get;}
public double  max{set;get;}
public double  min{set;get;}

	public soqlfunc()
	{
		count12 = [SELECT count()  FROM transaction__c ];
		AggregateResult[] groupedResults = [SELECT AVG(Amount__c) aver,sum(Amount__c) sum,max(Amount__c) max,min(Amount__c) min FROM transaction__c];

		avg = double.valueOf(groupedResults[0].get('aver'));
		sum = double.valueOf(groupedResults[0].get('sum'));  
		max = double.valueOf(groupedResults[0].get('max'));  
		min = double.valueOf(groupedResults[0].get('max'));
		
		System.debug('-------------min----->'+min);
		System.debug('-------------max----->'+max);
		System.debug('-------------sum---->'+sum);

	}

}

I have tested the same code on opportunity object in developer console working fine for me

User-added image

Please let us know if this will help you
 
prasanth puvvada 4prasanth puvvada 4
sorry amith.  no debug line in execution .  User-added image
prasanth puvvada 4prasanth puvvada 4
ok, amith. Now i created a class in developer console and saved it. Please tell me how to execute this ? thanks in advance
Amit Chaudhary 8Amit Chaudhary 8
Try to open workbanch and execute below code to test :-
Option 1:-
        integer count12;
		double  avg ; 
		double  sum;
		double  max;
		double  min;

		count12 = [SELECT count()  FROM transaction__c ];
		AggregateResult[] groupedResults = [SELECT AVG(Amount__c) aver,sum(Amount__c) sum,max(Amount__c) max,min(Amount__c) min FROM transaction__c];

		avg = double.valueOf(groupedResults[0].get('aver'));
		sum = double.valueOf(groupedResults[0].get('sum'));  
		max = double.valueOf(groupedResults[0].get('max'));  
		min = double.valueOf(groupedResults[0].get('max'));
		
		System.debug('-------------min----->'+min);
		System.debug('-------------max----->'+max);
		System.debug('-------------sum---->'+sum);

like below image :-
User-added image


Option 2:-  You can execute your class by executing below code in workbanch:-

soqlfunc obj = new soqlfunc();


 
Amit Chaudhary 8Amit Chaudhary 8
Please follow below Step :-
STEP 1:- Login on Workbanch 
STEP 2:- Then go to Apex Execute

User-added image
STEP 3:-  Exceute below code :-
soqlfunc obj = new soqlfunc();
or exceute below code :-
 
integer count12;
double  avg ; 
double  sum;
double  max;
double  min;

count12 = [SELECT count()  FROM transaction__c ];
AggregateResult[] groupedResults = [SELECT AVG(Amount__c) aver,sum(Amount__c) sum,max(Amount__c) max,min(Amount__c) min FROM transaction__c];

avg = double.valueOf(groupedResults[0].get('aver'));
sum = double.valueOf(groupedResults[0].get('sum'));  
max = double.valueOf(groupedResults[0].get('max'));  
min = double.valueOf(groupedResults[0].get('max'));

System.debug('-------------min----->'+min);
System.debug('-------------max----->'+max);
System.debug('-------------sum---->'+sum);


 
prasanth puvvada 4prasanth puvvada 4
yeah got it amith. thank you i executed in workbench. 
Amit Chaudhary 8Amit Chaudhary 8

Please let us know if above post will help you. Please mark the same as solution so that if any one have same issue this post will help