3

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

Richard N
  • 895
  • 9
  • 19
  • 36

4 Answers4

10

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)

mast0r
  • 820
  • 5
  • 13
  • I actually have done the exact same thing but looking at the list size. But I wanted to see if there was a better way. Anyways I will go with this as the answer. Thanks. – Richard N Aug 22 '12 at 15:16
  • I didn't see it at first, but there's a subtle difference between `count()` and `count(Id)` which changes return type from Int to `List` – specimen Jun 20 '19 at 13:50
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.

Tezyn
  • 1,314
  • 1
  • 10
  • 24
  • But what if I need to count the total number of grouped row. Say the SOQL without grouping returns 25 and with groping returns 10, How can I obtain 10? – Richard N Aug 21 '12 at 23:13
  • Adding the count from [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] will give the same total as [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] the only difference is the first one will break the total across the unique combinations of Company__c, Name, Payment_Date__c, Pay_Cycle_Type__c. The total will be the same. – Tezyn Aug 22 '12 at 13:53
  • Agree but I don't need the "count" but actual rows returned. A GROUP BY may consolidate 40 rows into 25 rows. I would need the 25. I had actually done what the mast0r has posted below. So I will mark that as the answer but thanks a lot for explaining. – Richard N Aug 22 '12 at 15:09
1

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

pica_chew
  • 11
  • 1
0

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?

Community
  • 1
  • 1
user1691706
  • 23
  • 1
  • 5