Saturday, April 3, 2010

GROUP BY Clause in salesforce SOQL

How to use GROUP BY clause in Salesforce

As you know, there are some good things in Spring’10 release of salesforce.com for developers, one of them for SOQL and yet a powerful feature is Introducing GROUP BY Clause and Aggregate functions like MAX(), SUM() etc.

People who are from SQL background or who know SQL are definitely, great appreciators of GROUP BY usage in SQL.

Till now, in salesforce if we want to achieve this feature, we were dealing with the apex code only.
The new syntax is listed below.
SELECT fieldList FROM objectType[WHERE conditionExpression]
[WITH [DATA CATEGORY] filteringExpression]
[GROUP BY fieldGroupByList] | [GROUP BY ROLLUP|CUBE (fieldSubtotalGroupByList)]
[HAVING havingConditionExpression]
[ORDER BY fieldOrderByList ASC | DESC ? NULLS FIRST | LAST ?]
[LIMIT ?]
As you observe on the above syntax, GROUP BY is of 3 types here.

GROUP BY Explained with Visualforce report:

Page:

<apex:page controller="TestGroupBy">
  <apex:pageBlock title="Test Group By">
      <apex:pageBlockTable value="{!Results}" var="ar">
          <apex:column headerValue="Number of Opportunities" value="{!ar.Total}"/>
           <apex:column headerValue="Close Date" value="{!ar.CloseDate}"/>
      </apex:pageBlockTable>
  </apex:pageBlock>
</apex:page>

Class:
public class TestGroupBy
{
public list<AggregateResult> lstAR = new list<AggregateResult>();
/*
Note that any query that includes an aggregate function returns its results in an array of AggregateResult objects. AggregateResult is a read-only sObject and is only used for query results.
Aggregate functions become a more powerful tool to generate reports when you use them with a GROUP BY clause. For example, you could find the count of all opportunities for a CloseDate.

*/
public TestGroupBy()
{
lstAR = [SELECT CloseDate, COUNT(id) Total FROM Opportunity GROUP BY CloseDate];
}

public list<OppClass> getResults()
{
list<OppClass> lstResult = new list<OppClass>();
for (AggregateResult ar: lstAR)
{
oppClass objOppClass = new oppClass(ar);
lstResult.add(objOppClass);
}
return lstResult;
}

class oppClass
{
public Integer Total
{ get;set; }

public Date CloseDate
{ get;set; }

public oppClass(AggregateResult ar)
{
//Note that ar returns objects as results, so you need type conversion here
Total = (Integer)ar.get('Total');
CloseDate = (Date)ar.get('CloseDate');
}
}
}


Result:


GROUPING, GROUP BY CUBE, GROUP BY ROLLUP,

These topics are better explained in the apex pdf.
To use them in visualforce, after writing the specific query, you can use the above Page and Class

10 comments:

  1. I have 2 different Enter Prise Edition AggregateResult not working in One EnterPrise Edition, In Another Enter Prise Edition working AggregateResult Function Object , Y it show lilke ,Any Idea

    ReplyDelete
  2. HI Srinivas,

    Please provide the test class too.

    Thanks,

    ReplyDelete
  3. How can I add it pagination?

    ReplyDelete
  4. Hi Srinivas This Topic helped me a Lot,Is there a way to provide a date manually and can we retrieve Opportunities on that Date Given ...

    ReplyDelete
  5. After hours of trying to find a good way of parsing these queries your post saved the day. Thank you so much Srinivas!

    ReplyDelete
  6. Very helpful indeed..!!

    ReplyDelete
  7. Still helping people. Thanks!

    ReplyDelete
  8. What would the controller code look like if you wanted to display another aggregated field. I'm struggling to transpose your code (which works great on its own) so that it shows two aggregated amounts. I'm not a coder so it's really challenging.

    ReplyDelete