Last week I offered a solution for sorting in Apex in response to some discussions taking place on the discussion boards .   As promised, I'm back to discuss one approach to addressing the business problem highlighted in a very detailed fashion in this thread. The relevant technical requirement outlined therein can be paraphrased as "How can I group, sum and order opportunity information in Apex?" And of course doing so with the minimal impact against the respective governor limits.

The end result should look something like this:

Let's address each of the goals in sequence.

1. Group

A notable limitation in SOQL, the Salesforce Object Query Language, is the inability to GROUP BY. Fortunately there is a technique for grouping data that happens to coincide with the business requirement being addressed.  That technique is to leverage the aggregate relationship functionality of SOQL. The following example will return accounts with their respective opportunities:

SELECT Name, (SELECT Name, Amount FROM Opportunities) FROM Account

"But doesn't this bring back accounts that have no opportunities too", you ask? Why yes, yes it does. Don't want those? Enter the Semi-joins capability of SOQL, new in the Winter '09 release coming soon to a cloud near you. With the following change (in bold), the result will only include accounts that have related opportunities:

SELECT Name, (SELECT Name, Amount FROM Opportunities) 
FROM Account WHERE Id IN (SELECT AccountId from Opportunity)

2. Sum

Now we have the opportunities grouped by account and are ready to total them up.  The end goal for this example is to present the information acquired through Apex on a Visualforce page. As such we need a temporary structure that provides access to the account, the opportunities and the summarized total.  This structure can be defined as a class in Apex:

public class accountTotal {
   public Account account { get; private set; }
   public Opportunity total { get; private set; }
   
   public accountTotal(Account a) {
       account = a;
       total = new Opportunity(amount = 0);
       for(Opportunity o:a.opportunities) {
           if(o.amount != null) {
             total.amount += o.amount;
           }
       }
   }
}

At this point you might be wondering why this class has an Opportunity member for the total rather than a primitive, such as Decimal.  Given the goal of outputting to a Visualforce page we can get the standard  currency formatting for free as long as we can bind to a field definition rather than just a simple primitive.

Now the following method will return a collection of the AccountTotal structures for each account in the query result:

public List<accountTotal> getAccountTotals(){
   List<accountTotal> accountTotals = new List<accountTotal>();

   for(Account a:[SELECT Name, (SELECT Name, Amount FROM Opportunities) 
                  FROM Account WHERE Id IN (SELECT AccountId from Opportunity)]) {
      accountTotals.add(new AccountTotal(a));
   }

   return accountTotals;
}

3. Order

So now we have just the accounts with opportunities (grouped) and their relevant totals but there's one last step to allow the user to see the most interesting accounts first. The following method will order the AccountTotal collection in descending order of the total using the approach described in my last post :

private List<AccountTotal> sortAccountTotals(List<AccountTotal> totals) {
   List<AccountTotal> returnList = new List<AccountTotal>();

   Map<Decimal, List<AccountTotal>> totalMap = new Map<Decimal, List<AccountTotal>>();
   
   for(AccountTotal t:totals) {
       if(totalMap.get(t.total.amount) == null) {
           totalMap.put(t.total.amount, new List<AccountTotal>());
       }
       totalMap.get(t.total.amount).add(t);
   }
   
   List<Decimal> keys = new List<Decimal>(totalMap.keySet());
   keys.sort();
   
   /* Sort puts things in ascending order so for descending iterate over
      the keys backwards. */
   for(Integer i = (keys.size()-1);i >= 0; i--) {
      returnList.addAll(totalMap.get(keys.get(i)));
   }
   
   return returnList;
}

Putting it all together

The following controller class definition combines each of the above requirements to enable a Visualforce page to display the desired information in the expected way:

public class accountsWithOpptysCon {
        
    public List<AccountTotal> getaccounttotals() {
        List<AccountTotal> accounttotals = new List<AccountTotal>();
        for(Account a:[select name, (select name,amount from opportunities) 
                from account 
                where id in (select accountid from opportunity)]) {

            accounttotals.add(new AccountTotal(a));
        }
        return sortAccountTotals(accounttotals);
    }
    
    public class accountTotal {
        public Account account { get; private set; }
        public Opportunity total { get; private set; }
        
        public accountTotal(Account a) {
            account = a;
            total = new Opportunity(amount = 0);
            for(Opportunity o:a.opportunities) {
                if(o.amount != null) total.amount += o.amount;
            }
        }
    }  
    
    private List<AccountTotal> sortAccountTotals(List<AccountTotal> totals) {
        List<AccountTotal> returnList = new List<AccountTotal>();
    
        Map<Decimal, List<AccountTotal>> totalMap = new Map<Decimal, List<AccountTotal>>();
        
        for(AccountTotal t:totals) {
            if(totalMap.get(t.total.amount) == null) {
                totalMap.put(t.total.amount, new List<AccountTotal>());            
            }
            totalMap.get(t.total.amount).add(t);
        }

        List<Decimal> keys = new List<Decimal>(totalMap.keySet());
        keys.sort();

        /* Sort puts things in ascending order so for descending iterate over
           the keys backwards. */
        for(Integer i = (keys.size()-1);i >= 0; i--) {
            returnList.addAll(totalMap.get(keys.get(i)));
        }

        return returnList;
    }
}

And finally, the Visualforce page markup for the solution as depicted above:

<apex:page controller="accountsWithOpptysCon" tabStyle="Account">
    <apex:pageBlock>
        <apex:pageBlockTable value="{!accounttotals}" var="a">
            <apex:column value="{!a.account.name}"/>
            <apex:column headerValue="Opportunity Amount Total" value="{!a.total.amount}"/>
            <apex:column headerValue="Opportunities">
                <apex:pageBlockTable value="{!a.account.opportunities}" var="o">
                    <apex:column value="{!o.name}"/>
                    <apex:column value="{!o.amount}"/>
                </apex:pageBlockTable>
            </apex:column>
        </apex:pageBlockTable>
    </apex:pageBlock>
</apex:page>

As some have pointed out, there are other ways of addressing this challenge depending on your specific requirements. For example, if you need the summed total value to be part of your data model and the filter is static then you should look at creating a roll-up summary field (search for "Roll-up Summary Field" within the help system in your account) and let the platform maintain the calculation for you.

In this case, recall that from the requirements this was meant to be used within an interactive interface where the user changes the filter criteria on the fly, something that can easily be added to this example with either Dynamic SOQL (also new in Winter '09) or through bind variables in the SOQL WHERE clause.

Keeping on top of all the latest features and techniques for efficiently achieving the needs of your business can be tough.  We'll do our best to help you here!  Until next time...