16

This is a basic SOQL question. I have a number of Application (custom) objects that are children to the User (standard) object.

I want to count the number of Applications associated with each User.

Looking at the documentation I can get a query like this to work:

SELECT Name, (SELECT id FROM Applications__r) FROM User

But how do I get a count associated with each User?

27

You could use your current query and get the size attribute of the list after the query finishes:

List<User> users = [SELECT Name, (SELECT id FROM Applications__r) FROM User];
for (User u : users) {
    // do something with it...
    u.Applications__r.size();
}

Alternatively, you can use an AggregateResult query which would query the child and group by the User Id lookup. This would not retrieve any information about Users that don't have any Applications, though. Also, if the User field is not required, all Applications that don't have a User would be put in the grouping where User__c is null.

// Assumes you have a Lookup called User__c
Map<Id, Integer> userAppCountMap = new Map<Id, Integer>();
for (AggregateResult aggRes : [
      SELECT COUNT(ID) numApps, User__c userId
      FROM Application__c
      GROUP BY User__c
]) {
    Id userId = (Id) aggRes.get('userId');
    Integer numApps = (Integer) aggRes.get('numApps');
    userAppCountMap.put(userId, numApps);
}
  • 1
    Your first example doesn't capture the fact that some users might have 0 cases assigned. But the second one works great. – powlo May 22 '14 at 11:25
  • 2
    Do not use 1st one unless you're sure the numbers will be low. For bigger cases the inner query starts to lie and limit itself to say 200 rows when the real amount can be bigger. It's bit similar to how related list by default show first 5 records and you need to explicitly click to get more. – eyescream May 22 '14 at 12:58
  • Hmm. There probably will be more than 200 applications. Is there any documentation for what you're describing? – powlo May 22 '14 at 14:26
  • 1
    From salesforce.com/us/developer/docs/apexcode/Content/… "The row counts from these relationship queries contribute to the row counts of the overall code execution." – powlo May 23 '14 at 10:01
  • What if I want the query to return count of Applications against each "user name" in an Ascending/Descending order? – sanchit Mar 19 '16 at 2:35
3

It is very easy to do with Apex code, But nobody put an SOQL queried Answer here, I searched in many blogs regarding this. Finally, I did it alone. Here it helps...

SELECT (Parent_Api_Name_In_Child_Object),
COUNT(ID)
   (Child_Realtionship_Name__r.Parent_Fields....)
     FROM (Child_Object_Api_Name) GROUP BY (Parent_Api_Name_Child_Object,
                        Parent Fields with API Names) HAVING COUNT(ID){>,<,=,{Optional}}
  • 1
    It seems like you just pasted the stuff from some other place, can you plz recheck and correct to make it readable. No need to add name and numbr. – Mahmood Mar 4 '17 at 20:29
  • This is great, thanks. I needed all records where the number of attachments was higher than 100. Used your template, and it worked nicely: select parentid, count(id) from Attachment group by ParentId having count(ID) > 100 order by count(id) desc – Kirill Yunussov Sep 18 '17 at 14:37
  • Maybe a real example would be helpful – d512 Sep 1 '18 at 3:15
3

I know that Ranga Ranga already posted an answer to the question but their answer is a generic pseudo query. Here is a much simpler query that you can actually copy and paste right into SoqlXplorer and it will run.

select User__c, count(Id)
from Applications__c
group by User__c

Works fine with other objects as well:

select AccountId, count(Id)
from Contact
group by AccountId

Note you might have to add a limit or having clause to get exactly what you want.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.