SOQL Group By – Grouping records in Salesforce.com

Overview

One of the most sought after features in SOQL (Salesforce.com Object Query Language) has been the inclusion of record grouping. While Aggregate functions such as Min, Max, Count, and Sum have been available, they have not been available to retrieve grouped Aggregate results. For example, if you wanted to retrieve the Count and Sum(Amount) of Opportunity Records grouped by an Account, you have to retrieve all of the Opportunities and create a loop to increment Counts and Sum based on the Account associated with the Opportunity. Ugly!
With the introduction of the Salesforce Platform Winter 2010 release and version 18 of the API, grouping and a few other functions are available. In this post I will concentrate on the “GROUP BY” clause. In future posts, I will examine other clauses such as:

• GROUP BY ROLLUP ()
• GROUPING ()
• GROUP BY CUBE ()
• HAVING ()

EXAMPLE

In this example, I want to showcase the different types of Grouping and Aggregate Functions that can be combined to come up with some interesting results. I want to create a Visual Force Page bound to a Custom Controller, where I can display the results of the SOQL statement.

First, let’s start with the Controller code. I wanted to bind the results of the query directly to a apex:dataTable, so I created an object to hold the Grouping values and Aggregated results. Below is the object:

public class GroupedQuery
{
    public string GroupBy1     {get;set;}
    public string GroupBy2     {get;set;}
    public string GroupBy3     {get;set;}
    public integer Count {get;set;}
    public Decimal Sum {get;set;}
    public Decimal Average {get;set;}
    public Decimal Min {get;set;}
    public Decimal Max {get;set;}
}

In addition, in order to bind an object to the dataTable within the Visual Force page, you need to expose a public member from your Controller code. For brevity sake, I just added a public List<> member like this:

public List<GroupedQuery> queryResults {get;set;}

Next in the Controller Constructor (keep in mind this is for demo purposes!) we use a SOQL query to retrieve a List of generic sObjects:

List<sObject> queries = [select Account.Name AccountName, Count(Name) CountResult
                                           from Opportunity group by Account.Name];

Once we have the Results we iterate through a loop to create our collection of GroupedQuery objects:

queryResults = new List<GroupedQuery>();
for (sObject query: queries)
{
    GroupedQuery myObject = new GroupedQuery();
    myObject.Count = Integer.valueOf(query.get('CountResult'));
    myObject.GroupBy1 = String.valueOf(query.get('AccountName'));
    queryResults.Add(myObject);
}

The variable queryResults is exposed as a public variable from the controller and thus we can tie this List directly to a dataTable and access the public properties of the GroupedQuery object contained in the List. Below is the Visual Force page code:

<apex:dataTable id="queryResults" value="{!queryResults}"
                var="queryResultsRow" border="1" width="100%">
    <apex:column >
        <apex:facet name="header">GroupBy1</apex:facet>
        {!queryResultsRow.GroupBy1}
    </apex:column>
    <apex:column >
        <apex:facet name="header">Count</apex:facet>
        {!queryResultsRow.Count}
    </apex:column>
</apex:dataTable>

The code above will output the following results (the Account data is data from the Developer Edition of Salesforce.com)

A more involved example is included below. This includes two levels of grouping, as well as Min, Max, Sum and Average functions:

Summary

As demonstrated, the new functionality released with the Salesforce.com API Version 18 with regards to Grouping is extremely powerful and addresses a pain point that many developers have had to overcome to achieve Rollup type queries. This information is contained within the What’s New notes for Salesforce.com Version 18. Keep in mind this is included with the Winter 2010 release of the Salesforce.com platform, so you will need to ensure that your organization has been upgraded in order to avail of this functionality.

Are you tired of working with consulting companies who give you what you ask for, but not what you need? If you want to partner with us to extend your IT capabilities and produce real results, contact us