2

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): enter image description here 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.

  • So Patient is a parent of infusion? This is a junction object? – sfdcfox Feb 27 '18 at 21:47
  • Nope- Patient is just a lookup on each Infusion. There are multiple infusions to each patient though. This architecture isn't something that's on the table to change. More I'm curious if anyone knows any SOQL tricks to deal with the relationships. Its irksome that you can return the needed data and see the relationship, but I can't find a way to grab what I need w/ SOQL. – Jake Feb 27 '18 at 22:09
3

You can use count_distinct to get the expected values:

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

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.