In my data structure I have a several custom objects that form a relationship to the Standard Salesforce Account Object.

I have three custom objects:

  • L_Account
  • L_Contact
  • L_Enrollment

L_Enrollment -> L_Contact -> L_Account -> Standard SF Account

I want to get a Count per Standard SF Account for how many L_Enrollment exist that match a creation date (on the L_Enrollment level)

Can this be done? I am familiar with SOQL Relationships but need a great amount of help on the syntax.

1 Answer 1


You could do it with aggregate queries. Making some assumptions about the API Names of your relationship fields:

Map<Id, Integer> accountIdToEnrollmentCount = new Map<Id, Integer>();
for (AggregateResult aggregate : [
    SELECT count(Id) records, L_Contact__r.L_Account__r.Account__c accountId
    FROM L_Enrollment__c WHERE CreatedDate = :2016-10-21
    GROUP BY L_Contact__r.L_Account__r.Account__c

What's going on here?

The map itself is fairly straightforward. Let's break down the query:

  • You can GROUP BY a specific field (even many relationships distant).
    • When you perform queries using this clause, they return List<AggregateResult>.
  • When you use this clause, each field must be grouped or aggregated.
    • With the relationship to Account you group it by including it in the GROUP BY clause.
    • With Id you count the number of populated values, which gives you the total number of enrollments you are looking for.
  • When you use this clause, you can alias any field in your SELECT clause, which can make your code a lot more concise and easier to wrap your head around!
    • Here we can now just call aggregate.get('accountId') even though the relationship is really L_Contact__r.L_Account__r.Account__c.
    • We can also get the count in an easy to understand way. Without aliasing, you would have to use aggregate.get('expr0'), which is ugly and doesn't scale very well.
  • Note that when working with AggregateResult, you have to use the SObject.get method, which returns an un-cast Object. That's why you have to cast the results ((Id), (Integer)).
  • Thanks Adrian, the logic makes sense. I swapped in my actual field names into the query successfully. To display the Map, will I need to create a VisualForce Page? I am less experienced with this, could you point me to some resources on building the remaining components to get this to display in a dashboard? Oct 21, 2016 at 23:11
  • You will need Visualforce, yes. I'd recommend taking a look at this Visualforce Cheatsheet, playing around with it a bit, and asking more questions on here if you get stuck. :)
    – Adrian Larson
    Oct 21, 2016 at 23:14

You must log in to answer this question.

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