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.

 

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.