CODEWITHSUNDEEP
salesforceapex-codesoqlforce.com
Richard N
Richard N

Reputation: 925

SOQL Aggregate query: Count number of rows returned

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

Upvotes: 3

Views: 34728

Answers (4)

pica_chew
pica_chew

Reputation: 11

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

Upvotes: 1

user1691706
user1691706

Reputation: 23

I don'y think aggr.size is what you need in

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

The count is returned in the expr0 field of the AggregateResult object. aggr.get('expr0')

Will work. If you have other field return with the count, please see the example in the following link

How do I display the results of an aggregate SOQL query on a Visualforce page?

Upvotes: 0

mast0r
mast0r

Reputation: 820

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)

Upvotes: 10

Tezyn
Tezyn

Reputation: 1344

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.

Upvotes: 2

Related Questions