0

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.

2
  • There's no way to do something like SELECT Id, Username, (SELECT Id FROM Opportunities) FROM User because the relationship name is unknown (really null when you call describe() on it). I've also experimented with FROM OwnedOpportunities etc., no success. Use Tanuj's or Priyanka's answer depending if you're fine with "if he's not there it means he has 0" or you need to build a full map (and add the entries with UserId => 0 manually).
    – eyescream
    Nov 26, 2012 at 12:56
  • can you please provide vf page to display
    – user41851
    Feb 14, 2017 at 10:57

2 Answers 2

3

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);
2
  • So are you saying I can't do an outer join?
    – dublintech
    Nov 26, 2012 at 13:49
  • In SOQL we don't have joins..
    – Priyanka
    Nov 26, 2012 at 13:52
3

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

1
  • This won't give me the Users who have zero opportunities
    – dublintech
    Nov 26, 2012 at 11:45

You must log in to answer this question.

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