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
James MeyerJames Meyer 

Sum a SubQuery in SOQL

Trying to come up with an SOQL query to sum last months sales of specific items.  In short, they need to be in our "Reporting" company vertical, but not include some products.

I'm new to SOQL, but experienced with SQL...which well, is a bit frustrating, because I could do this in my sleep in SQL.

So, I have this query:

SELECT
  (SELECT TotalPrice FROM OpportunityLineItems WHERE (not PriceBookEntry.Product2.Name like '%ancillary%') and (not PriceBookEntry.Product2.Name like '%listen%') )
FROM
  Opportunity
WHERE
  CloseDate = LAST_MONTH AND
  RecordType.Name = 'Reporting' AND  
  StageName = 'Closed Won'  AND
  Id NOT IN (SELECT OpportunityId FROM OpportunityLineItem WHERE PriceBookEntry.Product2.Name like '%ancillary%' or PriceBookEntry.Product2.Name like '%listen%' )

But, it's returning a dataset with an array.  I've tried doing a sum around the subquery, and I get an error (MALFORMED_QUERY).

How do I collapse the array to get a sum of the LineItems for those products?  

Thanks.

 
Best Answer chosen by James Meyer
JethaJetha
Similer to java, in salesforce also we have aggregation Query :


AggregateResult[] groupedResults
  = [SELECT SUM(TotalPrice) FROM OpportunityLineItems WHERE (not PriceBookEntry.Product2.Name like '%ancillary%') 
     AND (not PriceBookEntry.Product2.Name like '%listen%')
     AND Opportunity.StageName = 'Closed Won'
     AND Opportunity.RecordTypeId = Schema.getGlobalDescribe().get('Opportunity').getDescribe().getRecordTypeInfosByName().get('Reporting').getRecordTypeId()
     AND Opportunity.CloseDate > System.today().AddMonths(-1)];
     
for (AggregateResult ar : groupedResults)  {
    System.debug('TotalPrice' + ar.get('expr0'));
}


Replace your code with above Query and see the debug log, you will get the expected output............

All Answers

Shashikant SharmaShashikant Sharma
Hi James,

You need to use SOQL Aggregate Functions to do the SUM.

Like : 
  
SELECT SUM(Amount)
FROM Opportunity
WHERE IsClosed = false AND Probability > 60

https://developer.salesforce.com/docs/atlas.en-us.apexcode.meta/apexcode/langCon_apex_SOQL_agg_fns.htm
https://developer.salesforce.com/docs/atlas.en-us.soql_sosl.meta/soql_sosl/sforce_api_calls_soql_select_agg_functions.htm

Thanks
Shashikant

 
James MeyerJames Meyer
Wherever I put a SUM function, I get a malformed query.  Can't use it in the SubQuery and can't seem to use it around the subquery.
James MeyerJames Meyer
This is not supported: 
SELECT Sum(SELECT TotalPrice FROM OpportunityLineItems WHERE (not PriceBookEntry.Product2.Name like '%ancillary%') and (not PriceBookEntry.Product2.Name like '%listen%') )  FROM Opportunity WHERE CloseDate = LAST_MONTH AND RecordType.Name = 'Reporting' AND  StageName = 'Closed Won'  AND Id NOT IN (SELECT OpportunityId FROM OpportunityLineItem WHERE PriceBookEntry.Product2.Name like '%ancillary%' or PriceBookEntry.Product2.Name like '%listen%' )

And Neither is this:
SELECT (SELECT Sum(TotalPrice) FROM OpportunityLineItems WHERE (not PriceBookEntry.Product2.Name like '%ancillary%') and (not PriceBookEntry.Product2.Name like '%listen%') )  FROM Opportunity WHERE CloseDate = LAST_MONTH AND RecordType.Name = 'Reporting' AND  StageName = 'Closed Won'  AND Id NOT IN (SELECT OpportunityId FROM OpportunityLineItem WHERE PriceBookEntry.Product2.Name like '%ancillary%' or PriceBookEntry.Product2.Name like '%listen%' )
JethaJetha
Similer to java, in salesforce also we have aggregation Query :


AggregateResult[] groupedResults
  = [SELECT SUM(TotalPrice) FROM OpportunityLineItems WHERE (not PriceBookEntry.Product2.Name like '%ancillary%') 
     AND (not PriceBookEntry.Product2.Name like '%listen%')
     AND Opportunity.StageName = 'Closed Won'
     AND Opportunity.RecordTypeId = Schema.getGlobalDescribe().get('Opportunity').getDescribe().getRecordTypeInfosByName().get('Reporting').getRecordTypeId()
     AND Opportunity.CloseDate > System.today().AddMonths(-1)];
     
for (AggregateResult ar : groupedResults)  {
    System.debug('TotalPrice' + ar.get('expr0'));
}


Replace your code with above Query and see the debug log, you will get the expected output............
This was selected as the best answer
James MeyerJames Meyer
I'm not using Apex.  Need to be purely SOQL.  I'm executing a SOQL query from a Google Sheets script.
JethaJetha
Aggregation query is also a other form soql James Meyer replied to a question you're following at 6:14 AM on 4/8/2016. Original question: Trying to come up with an SOQL query to sum last months sales of specific items. In short, they need to be in our "Reporting" company vertical, but not include some products. I'm new to SOQL, but experienced with SQL...which well, is a bit frustrating, because I could do this in my sleep in SQL. So, I have this query: SELECT (SELECT TotalPrice FROM OpportunityLineItems WHERE (not PriceBookEntry.Product2.Name like '%ancillary%') and (not PriceBookEntry.Product2.Name like '%listen%') ) FROM Opportunity WHERE CloseDate = LAST_MONTH AND RecordType.Name = 'Reporting' AND StageName = 'Closed Won' AND Id NOT IN (SELECT OpportunityId FROM OpportunityLineItem WHERE PriceBookEntry.Product2.Name like '%ancillary%' or PriceBookEntry.Product2.Name like '%listen%' ) But, it's returning a dataset with an array. I've tried doing a sum around the subquery, and I get an error (MALFORMED_QUERY). How do I collapse the array to get a sum of the LineItems for those products? Thanks. Reply: I'm not using Apex. Need to be purely SOQL. I'm executing a SOQL query from a Google Sheets script. Tip! To respond, either reply to this email or click this link: https://developer.salesforce.com/forums/ForumsMain?id=906F0000000DEWa
James MeyerJames Meyer
Thanks.  Think I managed to get it into SOQL.

Now I just need to figure out why the numbers are wrong...  I wish reports had a "export SOQL Query" button...