I want to get the number of Opportunities for each User.

In SQL this would be something like

SELECT COUNT(*) FROM (
   SELECT User.ID FROM USER
       LEFT OUTER JOIN
   SELECT OwnerID FROM Opportunity
       ON User.ID = Opportunity.OwnerId
) GROUP BY USER.ID;

Any tips on how I do this in SOQL?

Thanks.

Attribution to: dublintech

Possible Suggestion/Solution #1

SELECT Ownerid, COUNT(Id) FROM Opportunity GROUP BY Ownerid

Attribution to: Tanuj Kumar Sharma

Possible Suggestion/Solution #2

If you want to find the list of users who has opportunities and also the users who does not have opportunity, the below code will help!

Map<Id,user> Mapuserids = new map<Id,user>([SELECT id from user]);
//list 'lstuserids' holds list of user ids present in the Org
set<id> lstuserids = Mapuserids.keyset();
//find the count of users in the org
system.debug('lstuserids size='+lstuserids.size());
set<id> OppOwnerIds = new set<id>();
list<id> UsersWithNoOpp = new list<id>();
//Using the below aggregate query, find the list of users who has the opportunities and add them in a set
AggregateResult[] groupedResults = [SELECT Ownerid ownrid, COUNT(Id) countid FROM Opportunity GROUP BY Ownerid];

for (AggregateResult ar : groupedResults)  
{
        OppOwnerIds.add((id)ar.get('ownrid'));
}
//Iterate through the list of users ids and find which users doesn't have an opportunity.

for (Id userid : lstuserids)  
{
   if(!OppOwnerIds.contains(userid))
   {
       UsersWithNoOpp.add(userid);
   }
}
System.debug('list of users size='+UsersWithNoOpp.size());
System.debug('list of users='+UsersWithNoOpp);

Attribution to: Priyanka
This content is remixed from stackoverflow or stackexchange. Please visit https://salesforce.stackexchange.com/questions/4764