I attempted using this query, however this gets all accounts that have one related opportunity in the 'New Opportunity' stage, but the account may have more related opportunities in any other stage. What I want is accounts with only one related opportunity, and that opportunity is in the 'New Opportunity' stage.

SELECT Count(Id), AccountId 
FROM Opportunity 
WHERE StageName = 'New Opportunity' AND AccountId != null 
AND Account.Assigned_Date__c < 2019-02-07
AND Account.Owner.UserRole.Name IN ('ADR', 'Direct', 'Portfolio') 
GROUP BY AccountId HAVING Count(Id) = 1

1 Answer 1


You may need a couple rollups here. The field names below should give you the idea. One field should count all children, the other should count only those whose StageName is 'New Opportunity'.

SELECT Id FROM Account WHERE Assigned_Date__c < ...
AND Owner.UserRole.Name IN (...)
AND Total_Opportunity_Count__c = 1
AND New_Opportunity_Count__c = 1

You must log in to answer this question.

Not the answer you're looking for? Browse other questions tagged .