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

Some useful links that related with count :

• COUNT() and COUNT(fieldName)
• Aggregate Functions
• Database.countQuery(query)

"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

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.