How to Use 'Group By' in Class in Salesforce

How to Use 'Group By' in class?

1.Construct new Class

public class SummaryObject{
    //create new class
    public String Name { get; private set; }// record Name
    public Decimal TotalAmt { get; private set; }// record amount
    public SummaryObject(AggregateResult ar) {
        TotalAmt = (Decimal) ar.get('TotalAmount');
        Name = (String) ar.get('Name');
    }
}

2.Definition

//Use in running SQL
private String soqlBudgetSum{get;set;}

//Use in picklist Value
public Set<String> setDeptCategory {set;get;}
public List<SummaryObject> Summaries{get;set;} 
public List<SummaryObject> SummariesP{get;set;} 

3.The following code can be added in sepcial Function

soqlBudgetSum='select Department__c Name,sum(Amount__c) TotalAmount from Object_API_NAME where Object_API_NAME.RecordType.DeveloperName LIKE \'RecordType1%\'';

setDeptCategory = new Set<String>();
//calculate total amount
TotalBA=0;
// Add 'Group By'
String BudgetSQL=soqlBudgetSum+'group by Department__c';
// Run SQL
AggregateResult[] groupedResults=Database.query(BudgetSQL);

Summaries = new List<SummaryObject>();
for (AggregateResult ar : groupedResults) {
    //Get department List without redundancy
    if(ar.get('Name') != null){
        setDeptCategory.add(String.valueof(ar.get('Name')));
    }
    //For display
    Summaries.add(new SummaryObject(ar));
}
//calculate total amount
for(SummaryObject so:Summaries){
    TotalBA=TotalBA+so.TotalAmt;
}

4.The following code can be added in Visualforce page.

<table>
 <tr style="text-align: center; font-weight:bold; font-size: 18px; " >
  <td  colspan="2" >Total :  {!TotalBA}</td>
  <apex:repeat value="{!Summaries}" var="summary">
  <tr style="{!IF(isnull(summary.Name),'display:none;','')} ;">
   <td style="font-weight:bold;text-align: center;  ">{!summary.Name} </td>
   <td style="text-align: right; "><apex:outputText value="{0, number, ###,###,###,###,##0.00}"><apex:param name="sortField" value="{!summary.TotalAmt}" /></apex:outputText></td> 
  </tr>
  </apex:repeat>
  </td>
 </tr>
</table>

5. Call following code to get picklist Value

public List<SelectOption> getSearchCategoryOptions() {
        List<SelectOption> searchCategoryOptions = new List<SelectOption>();
        
        searchCategoryOptions.add(new SelectOption('','--None--'));
        for(String category : setDeptCategory){
            searchCategoryOptions.add(new SelectOption(category, category));
        }        
        searchCategoryOptions.sort();
        return searchCategoryOptions;
    }

6.'Searching Button':The following code can be added in Visualforce page.

<td width="100px">
    <apex:outputLabel style="font-weight:bold;" >    Department: </apex:outputLabel><br/>
    <apex:selectList value="{!Dept}" multiselect="false" size="1">
        <apex:selectOptions value="{!searchCategoryOptions}"/>
    </apex:selectList>
</td> 

Thanks for reading this article.
Any code samples provided here are purely for experimental purposes and comes with no warranty or support. The primary purpose of this article is just to share the knowledge and my own experience. Nothing more, nothing less. Use this code and approach at your own risk.Please feel free to contact me (yamshingkwan@gmail.com) if you have any inquiry or the code have any problem. Thank you.