"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 |
 |
| 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
1 | String QueryString = |
2 | 'SELECT count() FROM Account'; |
3 | Integer 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.
