You need to sign in to do that
Don't have an account?
Andrew Aldis
Calculations in SOQL
I am trying to write a soql query that divides the sum value of the amount on an opportunity with the max value of a sales commitment on the opportunity. I need the calculation to be done in the query if at all possible. I basically to pull the sum from one field, and divide it by the max of another. The query I am using is below.
Select Owner.UserRole.Name, SUM(Total_Net_Software_Reporting__c),Max(Owner.Director_Monthly_Commit_Software__c) ,SUM(Total_Net_Software_Reporting__c)/Max(Owner.Director_Monthly_Commit_Software__c) as TNS_Commit_To_Actual
FROM Opportunity
Where Owner.Sales_Rep__c=True AND CloseDate=This_Month
Group By Owner.UserRole.Name
Select Owner.UserRole.Name, SUM(Total_Net_Software_Reporting__c),Max(Owner.Director_Monthly_Commit_Software__c) ,SUM(Total_Net_Software_Reporting__c)/Max(Owner.Director_Monthly_Commit_Software__c) as TNS_Commit_To_Actual
FROM Opportunity
Where Owner.Sales_Rep__c=True AND CloseDate=This_Month
Group By Owner.UserRole.Name
Unfortunately you can't manipulate fields like that in SOQL ( you can in SQL but not SOQL).
You can select the SUM and MAX then use APEX logic to do the data manipulation.
Thx