function readOnly(count){ }
Starting November 20, the site will be set to read-only. On December 4, 2023,
forum discussions will move to the Trailblazer Community.
+ Start a Discussion
m_a_sridharm_a_sridhar 

Multiple aggregate functions in one SOQL query?

I'm trying to create a report via a single SOQL query from which I can retrieve fields from a parent object and two or more child objects. For example, suppose I want a report that gets me the account name, the number of assets attached to that account, the number of contacts for that account and the number of opportunities for that account. 

 

If I only want the account name and contact count, I could use

 

select count(Id), Account.Name from Contact group by Account.Name

 

And similarly I could get individual counts for the other objects. But this approach is not quite desirable: It needs multiple queries, it doesn't get me accounts that have no contacts, and it requires me to stitch together the results of multiple queries to make my report.

 

But if I try this, along the lines ot traditional SQL:

 

select Name, (select count(Id) from Contact), (select count(Id) from Asset), (select count(Id) from Opportunity)

 

then we have an invalid SOQL query, because SOQL does not support aggregate functions in child objects.

 

Any ideas? Perhaps I am missing something?

 

Thanks in advance.

Sridhar

Apsona.com