function readOnly(count){ }
Sign Up ›
Starting November 20, the site will be set to read-only. On December 4, 2023,
forum discussions will move to the Trailblazer Community.
Learn More

Browse by Topic
ShowAll Questionssorted byDate Posted
+ Start a Discussion
duke1duke1 

Embedded Calculation in select statement using aggregate

I am trying to write a select statement like this:

 

[select ID, sum(field1) sum1, sum(field2) sum2, (sum1/sum2) from Account Group by ID]

 

Is it possible to embed a calculation like (sum1/sum2) in a select statement when I'm using aggregate functions?  Or can someone recommend how you could so this in an SOQL statement?  Thanks.

Aaron2010Aaron2010

[select ID, sum(field1) sum1, sum(field2) sum2, (sum1/sum2) from Account Group by ID]

AggregateResult[] fieldSum = [SELECT Id, sum(field1), sum(field2) FROM Account GROUP BY Id];
Map<Id,Double> idDivisions= new map<Id,Double>();
for(AggregateResult ar : fieldsum){
id id1 = ar.Id;
Double s1= (double) ar.get('expr1');
Double s2= (double) ar.get('expr0');
Double div= s2/s1;
idDivisions.put(id1 ,div);
}

 

Hopefully this shoud work for you.
Aaron2010Aaron2010

Ignore the first line of soql query in the previous post...

 

AggregateResult[] fieldSum = [SELECT Id, sum(field1), sum(field2) FROM Account GROUP BY Id];
Map<Id,Double> idDivisions= new map<Id,Double>();
for(AggregateResult ar : fieldsum){
id id1 = ar.Id;
Double s1= (double) ar.get('expr1');
Double s2= (double) ar.get('expr0');
Double div= s2/s1;
idDivisions.put(id1 ,div);
}

 

Hopefully this shoud work for you.
duke1duke1

Thanks for your response.  Without my going into a long explanation why, I was wondering if it is possible to do the division within the SOQL statement itself?
Aaron2010Aaron2010

No, It is not possible. That was an alternative for capturing the divisions into corresponding Id's.
duke1duke1

Thanks.