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
Oliver Jones RajkumarOliver Jones Rajkumar 

Count() Closed Won and Closed Lost in a Single SOQL

Hi All,

    Is it possible to build a SOQL query to retrieve/show the count of closed and open opportunities under each accounts? I would to execute this code in the query editor in developer console.

Help and Thanks. 
Best Answer chosen by Oliver Jones Rajkumar
sfdc novicesfdc novice
Hi Oliver,

Create Two Rollup Summary fields on Account Object 

1) No of Open Opportunities (Using COUNT() Function )

2) No of Closed Opportunities (Using COUNT() Function )

Then Write an SOQL Query to retrieve No of Open Opportunities & No of Closed Opportunities for an Account.

List<Account> accounts = [SELECT Id,Name,No_of_Open_Opportunities__c,No_of_Closed_Opportunities__c FROM Account];

system.debug('=====No of Accounts=========' + accounts);


Thanks.

All Answers

Abhilash Mishra 13Abhilash Mishra 13
Hi Oliver,
it can be done using size() fuction of list class here is the sample code.
  
list<account> li=[select id,(select id from opportunities where StageName='Closed Won') from account limit 50000];
map<id, integer> counts=new map<id, integer>();
for(account acc:li){
integer n =acc.opportunities.size();
counts.put(acc.id, n);
}
Now you counts have it all.

Hope this helps :)
 
yogesh_sharmayogesh_sharma

Hi Oliver,

Try this one:

//Get All Closed Opportunity related to Account
map<Id, list<Opportunity>> mapAccountId_Opportunities = new map<Id, list<Opportunity>>();

//Get All Closed Opportunity Size
map<Id, integer> mapAccountID_ClosedOpportunitySize = new map<Id, integer>();

For(Account objAcc : [select Id, Name,
                      (select Id from Opportunities where StageName='Closed Won') from Account Limit 50000] ){
                 mapAccountId_Opportunities.put(objAcc.Id, objAcc.Opportunities) ;
                 mapAccountID_ClosedOpportunitySize.put(objAcc.Id, objAcc.Opportunities.size());
    }

system.debug('-mapAccountId_Opportunities-'+mapAccountId_Opportunities);
system.debug('-mapAccountID_ClosedOpportunitySize-'+mapAccountID_ClosedOpportunitySize);


Same you can do for Open Opportunities and if you want to result in a single Query, you can add filter with AND operator StageName='Open'

I hope it helps you. Please mark this as best answer if it helps you, so that it will be easy for others to find out easily.

Thanks,

Yogesh Sharma
sfdc novicesfdc novice
Hi Oliver,

Create Two Rollup Summary fields on Account Object 

1) No of Open Opportunities (Using COUNT() Function )

2) No of Closed Opportunities (Using COUNT() Function )

Then Write an SOQL Query to retrieve No of Open Opportunities & No of Closed Opportunities for an Account.

List<Account> accounts = [SELECT Id,Name,No_of_Open_Opportunities__c,No_of_Closed_Opportunities__c FROM Account];

system.debug('=====No of Accounts=========' + accounts);


Thanks.
This was selected as the best answer