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

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

2 Answers 2

5

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.

1
  • 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? Commented Jun 11, 2015 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!

2
  • Is this written in Java?
    – leeand00
    Commented Jul 11, 2019 at 15:04
  • @leeand00 APEX looks mostly like a superset of Java.
    – ps2goat
    Commented Jun 14, 2021 at 20:12

You must log in to answer this question.

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