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,
