Group BY IN SOQL:

Group BY IN SOQL:
This example shows how you can group records on a field.The following example is a straight and simple explains how to group records on closed date for opportunities list.
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');
}
}
}


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