SOQL Aggregate query: Count number of rows returned

Question

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

Solution 1

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)

Solution 2

Grouping returns a row for each unique value in your grouping fields.

Salesforce Group By documentation

If all you want the total of Payroll_Group_Detail__c created after 7/21/12 where the Tax_Branch__c is blank but has Total_Tax_Amount__c set, just loose the Group By statement.

Solution 3

For any other poor souls like myself who are looking for help with this -- If you did not use an alias after the aggregate function, use this:

Object counter = aggr[0].get('expr0');

If you did use an alias after the aggregate function, you can use this:

Object counter = aggr[0].get('myalias');

See the docs: Working with SOQL Aggregate Functions