Friday, December 29, 2017

Deep study on Salesforce count() vs count(fieldName)

Some useful links that related with count :


"Count is used to discover the number of rows that a query returns. "
You will notice , there two versions of syntax for COUNT():

  • count()
  • count(fieldName)

I experienced using both but I am not really deep into WHY COUNT() and WHY COUNT(fieldName) until today I did some reading and testing.I found the differences is quite interesting.

Difference in Query Editor result :

COUNT()
No field return.It directly say Total Rows:17
COUNT(fieldName)
Total Rows:1 and it returns field which 17.

Difference in Apex code :

COUNT()
Can be assigned to Integer directly.

Integer count =[SELECT COUNT() FROM Contact WHERE Account.Name Like 'Test%' ];
system.debug('count '+ count);

COUNT(fieldName)
You cannot assigned it to Integer or object, you must assign it to AggregateResult.It will throw error if you try to assign it Integer or object.

List countWithFieldName =[SELECT COUNT(Name) FROM Contact WHERE Account.Name Like 'Test%' ];
system.debug('countWithFieldName '+ countWithFieldName);

Extra use and limitation :


COUNT()

  • It is simple, JUST TO RETURN THE NUMBER OF ROW.That's it.The ability to be assigned directly to Integer make it better for that purpose.
  • It cannot be used with other field in query example
Select COUNT(),Name from Account will throw error.
  • It cannot be used with ORDER BY.
  • It cannot be used with GROUP BY for API version 19.0 later
  • Database.countQuery also serves the same purpose
1String QueryString =
2    'SELECT count() FROM Account';
3Integer i =
4    Database.countQuery(QueryString);


COUNT(fieldName)

  • It provide extra benefit if we want to generate reports for analysis by adding GROUP BY.
  • ORDER BY need to be used together with GROUP BY.
  • As you can see, you can have other field together with COUNT in SELECT statement.

Useful for reporting

What do you think?
Let me know.





No comments:

Post a Comment