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
Frances WottonFrances Wotton 

Syntax error for Apex trigger -

Am getting error from the following - can anyone advise correct syntax for subtracting two sum amounts, many thanks
Error: Compile Error: Missing 'SELECT' at 'sum' at line 35 column 50


    for(AggregateResult q : [select Project__c, (sum(Amount) - sum(npe01__Amount_Outstanding__c) )
        from Opportunity where (StageName = 'Complete - Won' or StageName = 'Agree Payment Schedule'  or StageName = 'Sign Contract'
        or StageName = 'Pledged' or StageName = 'Funding approved') and Project__c != null and Project__c IN :ProjectIds group by Project__c])
Best Answer chosen by Frances Wotton
Bhargavi TunuguntlaBhargavi Tunuguntla
Hi 

Try the below code:
  
for(AggregateResult q : [select Project__c, sum(Amount)  amt, sum(npe01__Amount_Outstanding__c)  amt1
        from Opportunity where (StageName = 'Complete - Won' or StageName = 'Agree Payment Schedule'  or StageName = 'Sign Contract'
        or StageName = 'Pledged' or StageName = 'Funding approved') and Project__c != null and Project__c IN :ProjectIds group by Project__c])
   {
       
	   decimal value=((decimal)q.get('amt')-(decimal)q.get('amt1'));
   }

Thanks.

All Answers

Naresh YadavNaresh Yadav
Hi Frances,

You can not do that in a query. For that, you need to query them and then perform the operation on it. Like below:
  
for(AggregateResult q : [select Project__c, sum(Amount) AmountSum1sum(npe01__Amount_Outstanding__c)  AmountSum2 from Opportunity where (StageName = 'Complete - Won'or StageName = 'Agree Payment Schedule'  or StageName = 'Sign Contract' OR StageName = 'Pledged'or StageName = 'Funding approved') and Project__c != null and Project__c IN :ProjectIds group byProject__c]){

    Integer result = q.get('AmountSum1') - q.get('AmountSum2');

}
Let me know if it helps you out. And mark it as best.


Thanks
Naresh Y. 
Bhargavi TunuguntlaBhargavi Tunuguntla
Hi 

Try the below code:
  
for(AggregateResult q : [select Project__c, sum(Amount)  amt, sum(npe01__Amount_Outstanding__c)  amt1
        from Opportunity where (StageName = 'Complete - Won' or StageName = 'Agree Payment Schedule'  or StageName = 'Sign Contract'
        or StageName = 'Pledged' or StageName = 'Funding approved') and Project__c != null and Project__c IN :ProjectIds group by Project__c])
   {
       
	   decimal value=((decimal)q.get('amt')-(decimal)q.get('amt1'));
   }

Thanks.
This was selected as the best answer
Frances WottonFrances Wotton
Thank you both, that has worked fine.