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:

“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:

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:

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:

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 :

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:

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

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…