4

Much like this post here: SOQL: Avoid count with NULL results return 0 (zero) instead

I have an instance where I am using aggregate functions like COUNT() and MAX() on a collection of Opportunities. These work just fine as long as my WHERE conditions don't filter out all opportunities, unfortunately if there are no rows to aggregate then my query returns 0 rows rather than returning 1 row as expected with the aggregate columns equal to '0'

The answer in the original response spoke of the coalesce() function. From what I can tell that is not a Salesforce method available to me. So is there an equivalent? How do I get my expected row returned with 0 values rather than null?

Here is an example of my SOQL:

SELECT AccountId, COUNT(Id) Invoices, AVG(Days_Past_Due__c) Average, Max(Days_Past_Due__c) Max
FROM Opportunity
WHERE AccountId IN :accountIds
AND IsDeleted = FALSE
AND {more conditions}
GROUP BY AccountId 
ORDER BY AccountId ASC NULLS FIRST

Thanks in advance for any help you can provide here

  • Did you try GROUP BY ROLLUP? – sfdcfox Jun 11 '15 at 3:15
  • not sure how to do that – Xtremefaith Jun 11 '15 at 3:15
  • Never mind. I was thinking it might work. It doesn't. – sfdcfox Jun 11 '15 at 3:50
4

No. There's no way to get the results you're looking for. SOQL is a very limited query language that isn't as robust as full SQL, although it handles relationships easier than SQL.

|improve this answer|||||
  • So is my only option to not use aggregate SOQL functions and instead return a collection of Opportunities that I manually iterate through and calculate count, average and max values? – Xtremefaith Jun 11 '15 at 18:59
1

Based on @sfdcfox's answer I decided the only option to accomplish what I needed was to abandon SOQL aggregate columns and handling AggregateResult object. Instead because requirements of this task (need AVG of all invoice opportunities, but MAX of only unpaid invoice opportunities), also in order to avoid some complicated version of multiple SOQL statements, my solution is to simply collect all Opportunities in relation to my List<String> of AccountIds and aggregate the data manually. For reference in case it helps anyone, here is a summation of my code:

public static void rollupAverageDaysPastDue (Set<String> accountIds){
    Set<Opportunity> all_invoices = new Set<Opportunity>([
        SELECT Id, AccountId, Days_Past_Due__c, Is_Paid__c
        FROM Opportunity
        WHERE AccountId IN :accountIds
        AND [is an invoice opportunity]
    ]);

    Map<Id,Integer> invoice_count = new Map<Id,Integer>();
    Map<Id,Decimal> invoice_sum = new Map<Id,Decimal>();
    Map<Id,Decimal> invoice_avg = new Map<Id,Decimal>();
    Map<Id,Decimal> invoice_max = new Map<Id,Decimal>();

    for(Opportunity opp : all_invoices) {
        /* Count every account opportunity */
        if( invoice_count.get(opp.AccountId) != NULL ){
            invoice_count.put(opp.AccountId, invoice_count.get(opp.AccountId)+1 ); // increment current count for this account ID
        } else {
            invoice_count.put(opp.AccountId,1);
        }

        /* Sum every account opportunity's Past Due Days */
        if( invoice_sum.get(opp.AccountId) != NULL ){
            /* add this opportunity to this account id summation */
            invoice_sum.put(opp.AccountId, invoice_sum.get(opp.AccountId)+opp.Days_Past_Due__c );
        } else {
            invoice_sum.put(opp.AccountId, opp.Days_Past_Due__c);
        }

        /* Sum every account opportunity's Past Due Days */
        if( opp.Is_Paid__c == FALSE ){
            if( invoice_max.get(opp.AccountId) != NULL ){
                if( invoice_max.get(opp.AccountId) <= opp.Days_Past_Due__c ){                         invoice_max.put(opp.AccountId, opp.Days_Past_Due__c);
                }
            } else {
                invoice_max.put(opp.AccountId, opp.Days_Past_Due__c);
            }
        }

    }

    /* Calculate average past due days for all invoices per account */
    for(Id aid : invoice_count.keySet()){
        Decimal average = invoice_sum.get(aid) / invoice_count.get(aid);
        invoice_avg.put(aid, average);
    }

    /* Iterate through account IDs to make a bulkified DML UPDATE */
}

Hope that can help someone stuck in similar situation. Good luck!

|improve this answer|||||
  • Is this written in Java? – leeand00 Jul 11 '19 at 15:04

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.