GROUP BY - Count Method in Apex Controller

Scenario:
Need to do Totals of Stages in Opportunity for the entire org.

Thought Process:

1. What are the columns involved? Answer: StageName and Count or Total
2. What methods will we utilize to achieve these results? Answer: Group By SOQL Statement with Count.
3. What UI to built this neat logic? Answer: Apex Controller and VisualForce Page
4. Need to achieve this type of reporting for the user:

Solution:

1. Use Eclipse IDE or SOQL Explorer to create your SOQL Statement.

2. SELECT StageName, Count(Name) ce 

   FROM Opportunity GROUP BY StageName

3. Second create  Apex Controller name what you like I named it TTL_Lesson, with your logic

public class TTL_Lesson{

//Define your variables
public class OppStageHolder {
    public String OPP {get; set;}
    public Integer TTL_Opp {get; set;}
//Empty Array    
public OppStageHolder (){}
}
//Results will be placed within this List
public List queryResults{ get; set; }

//Your Page
public PageReference TTL() {

AggregateResult[] groupedResults = [SELECT StageName, 

     Count(Name) ce FROM Opportunity 

     GROUP BY StageName];  
System.Debug('zzavg ' + groupedResults.size());
//Define your List
queryResults = new List();

for (AggregateResult ard : groupedResults)  {    
    OppStageHolder myObject = new OppStageHolder();    
    myObject.OPP = String.valueOf(ard.get('StageName'));    
    myObject.TTL_Opp = (Integer) ard.get('ce');         
    queryResults.add(myObject);    
}
return Page.TTL;
}

}

Now create your VF Page to call this and display it whenever the user clicks on this page:

<apex:page controller="TTL_Lesson" action="{!TTL}" showHeader="false" sidebar="false">
    <apex:dataTable value="{!queryResults}" var="a" id="theTable" border="2" cellpadding="1" cellspacing="1" bgcolor="#A9D0F5" >
                <apex:column >
                        <apex:facet name="header">Stage</apex:facet>
                        <apex:outputText value="{!a.OPP}"/>
                </apex:column>               
                <apex:column >
                        <apex:facet name="header">&nbsp;&nbsp;&nbsp;Count</apex:facet>
                        <apex:outputText value="{!a.TTL_Opp}"/>
                </apex:column>
    </apex:dataTable>
</apex:page>

End result is the image on top

How to use apex variable for total records in Visualforce

Scenario:
  I came across a issue where visualforce does not allow one to Count or Sum records in a page.
One solution would be to add more code to the controller to do a count of the records. Which is ok.
A simple solution is to use the apex variable function in Visualforce.

Solution:

1. Lets do it off Contacts
2. In your Apex Controller : Create a SOQL query as is:

public class countcontroller{

            public List<Contact> queryResult {get;private set;}

            public String qryString {get;set;}

            public PageReference query(){

            qryString =  'SELECT Name, Email, Phone from Contact';

            queryResult = Database.query(qryString);
             return null;

       }

} 

Pretty Simple and Straight Forward.

Now for the VF Page and Magic:

You will see I use the apex variable function to do a couple of things:

• 
  

  create a variable

  run the query inside that variable counting all the records by 1 within a repeat tag

  calling the variable with the total 

  Kind of like a for Loop but in Visualforce instead of controller. 

<apex:page standardcontroller="Contact" extensions="countcontroller">

<table width="95%" border="0" align="center" cellpadding="0" cellspacing="0">
<tr height="5">
    <td width="35%" class="outsideround_head" align="right">
        Total Contacts Returned:&nbsp;
    </td>
    <td width="8%" class="outside_round_head_value">
        <apex:variable var="call" value="{!0}" />
        <apex:repeat var="countitall" value="{!queryResult}" >
        <apex:variable var="call" value="{!call+1}"/>
        </apex:repeat>
        <apex:outputText value="{!call}"/>
    </td>

</tr>

</table>

</apex:page>

Wallah you have count.

Thanks please give me feedback!!! 

Thanks
Check out my Other Salesforce.com Blogs
Salesforce Made Easy

Salesforce Data Migration Made Easy
eTechCareers.com Coming Soon