+ Start a Discussion
ravi chandra 16ravi chandra 16 

SOQL Query

Write a SOQL query that counts the number of active Contacts for each Account in a set.
Bhanu MaheshBhanu Mahesh
Hi Ravi,

Try this
Set<Id> stAccIds = new Set<Id>(); //Suppose this set has Account Ids
Map<Id,Integer> accountIdWithContactCount = new Map<Id,Integer>();
for(Account acc : [SELECT Id,Name,(SELECT Id FROM Contacts WHERE Active__c = true) FROM Account WHERE Id IN :stAccIds]){
	accountIdWithContactCount.put(acc.Id,acc.Contacts.size());
}

Now this map accountIdWithContactCount will have count of contacts for each account

You can get the count by passing account Id
Integer count = accountIdWithContactCount.get(accId);

For relationship Queries refer the below links
https://developer.salesforce.com/blogs/developer-relations/2013/05/basic-soql-relationship-queries.html

http://www.salesforce.com/us/developer/docs/dbcom_soql_sosl/Content/sforce_api_calls_soql_relationships.htm

Regards,
Bhanu Mahesh
ravi chandra 16ravi chandra 16
Thank you Bhanu
Mark Tyler CrawfordMark Tyler Crawford
This is good, but is there a way to do this purely in SOQL?
Endrit M. SinoEndrit M. Sino
The query to achieve this is: 
AggregateResult[] groupedResults = [SELECT COUNT(Id) total, AccountId FROM Contact WHERE IsActive = true GROUP BY AccountId];