Use SOQL aggregation result to count number of related objects

Question

I have a fun puzzle here, and would like to see if anyone has a better solution. There are 3 objects: Account, Infusion__c, Patient__c. Infusion__c is a child of Accounts, and Infusion__c has a lookup relationship to Patient__c. There is no direct relationship between Patient__c and Account. I need a count of how many Patient objects there are for each account.

Is there any way to do this in a single SOQL statement? So far the best I've been able to do is query for the number of unique Patients in each account: SELECT COUNT(Patient__r.Id),Patient__c,Account__c FROM Infusion__c GROUP BY Account__c,Patient__c LIMIT 2000 This gives me the following (screenshot limited to one Account__c result): I can then iterate through this Aggregate result and add the records to a map of Account ids, and keep a tally saved.

In the image you can see that the COUNT results in the number of Infusions tallied, and cannot recognize distinct Patient records.

Is there a way to do this without iterating through the results? Any assistance appreciated. If anyone is wondering 'why', it's because I've spent some time on this and am curious, I understand one iteration through a list in a batch job won't cause too many issues.

Answer 1

You can use count_distinct to get the expected values:

SELECT COUNT_DISTINCT(Patient__c), Account__c FROM Infusion__c GROUP BY Account__c