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?
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);
}
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.
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}}
SELECT AccountId,Account.Name, COUNT(Id) ContactCount FROM Contact GROUP BY AccountId,Account.Name HAVING COUNT(Id) > 10
