SOQL Aggregate query: Count number of rows returned

Richard N picture Richard N · Aug 21, 2012 · Viewed 31.1k times · Source

The following is my SOQL query:

select COUNT(Id) FROM Payroll_Group_Detail__c where Tax_Batch__c=null and CreatedDate >=2012-07-21T00:00:00-05:00 and Total_Tax_Amount__c!=null GROUP By Company__c,Name,Payment_Date__c,Pay_Cycle_Type__c;

I am was trying to count the number of rows returned by this group by clause. But instead of getting one count, I get multiple rows. How to get the overall count of rows returned by this grouping?

Thanks, Calvin

Answer

mast0r picture mast0r · Aug 22, 2012

Just counting records:

Integer counter = [ Select count() 
                    FROM Payroll_Group_Detail__c 
                    Where Tax_Batch__c = null 
                    And CreatedDate >= 2012-07-21T00:00:00-05:00 
                    And Total_Tax_Amount__c != null ];

System.debug('My counted records: ' + counter);

With a GROUP BY:

AggregateResult[] aggr = [ Select count(Id) 
                           FROM Payroll_Group_Detail__c 
                           Where Tax_Batch__c = null 
                           And CreatedDate >= 2012-07-21T00:00:00-05:00 
                           And Total_Tax_Amount__c != null
                           Group By Total_Tax_Amount__c ];

Integer counter = Integer.valueOf(aggr.size());

System.debug('#### counter: ' + counter);

But remember, you can not count more than the allowed governor limit (Total number of records retrieved by SOQL queries -> 50,000)