COUNT() and COUNT(fieldName)

COUNT()

COUNT() returns the number of rows that match the filtering conditions.

For example:

SELECT COUNT()
FROM Account
WHERE Name LIKE 'a%'

SELECT COUNT()
FROM Contact, Contact.Account
WHERE Account.Name = 'MyriadPubs'

For COUNT(), the query result size field returns the number of rows. The records field returns null.

Note the following when using COUNT():

• COUNT() must be the only element in the SELECT list.
• You can use COUNT() with a LIMIT clause.
• You can't use COUNT() with an ORDER BY clause. Use COUNT(fieldName) instead.
• You can't use COUNT() with a GROUP BY clause for API version 19.0 and later. Use COUNT(fieldName) instead.

COUNT(fieldName)

COUNT(fieldName) returns the number of rows that match the filtering conditions and have a non-null value for fieldName. This syntax is newer than COUNT() and is available in API version 18.0 and later.

For example:

SELECT COUNT(Id)
FROM Account
WHERE Name LIKE 'a%'

COUNT(Id) returns the same count as COUNT(), so the previous and next queries are equivalent:

SELECT COUNT()
FROM Account
WHERE Name LIKE 'a%'

Note

COUNT(Id) in SOQL is equivalent to COUNT(*) in SQL.

For COUNT(fieldName), the AggregateResult object in the records field returns the number of rows. The size field does not reflect the count. For example:

SELECT COUNT(Id)
FROM Account
WHERE Name LIKE 'a%'

For this query, the count is returned in the expr0 field of the AggregateResult object. For more information, see Using Aliases with GROUP BY.

There are advantages to using COUNT(fieldName) instead of COUNT(). You can include multiple COUNT(fieldName) items in a SELECT clause. For example, the following query returns the number of opportunities, as well as the number of opportunities associated with a campaign.

SELECT COUNT(Id), COUNT(CampaignId)
FROM Opportunity

Unlike COUNT(), you can use a GROUP BY clause with COUNT(fieldName) in API version 18.0 and later. This allows you to analyze your records and return summary reporting information. For example, the following query returns the number of leads for each LeadSource value:

SELECT LeadSource, COUNT(Name)
FROM Lead
GROUP BY LeadSource