Caché SQL Reference
COUNT
[Home] [Back] [Next]
InterSystems: The power behind what matters   
Class Reference   
Search:    

An aggregate function that returns the number of rows in a table or a specified column.
Synopsis
COUNT(*)

COUNT([ALL | DISTINCT [BY(col-list)]] expression [%FOREACH(col-list)] [%AFTERHAVING])
Arguments
* Specifies that all rows should be counted to return the total number of rows in the specified table. COUNT(*) takes no other arguments and cannot be used with the ALL or DISTINCT keywords. COUNT(*) does not take an expression argument, and does not use information about any particular column. COUNT(*) returns the number of rows in a specified table or view without eliminating duplicates. It counts each row separately, including rows that contain NULL values.
ALL Optional — Specifies that COUNT return the count of all values for expression. This is the default if no keyword is specified.
DISTINCT Optional — A DISTINCT clause that specifies that COUNT return the count of the distinct (unique) values for expression. Cannot be used with a stream field. DISTINCT can specify a BY(col-list) subclause, where col-list can be a single column name or a comma-separated list of column names.
expression Any valid expression. Usually the name of a column that contains the data values to be counted.
%FOREACH(col-list) Optional — A column name or a comma-separated list of column names. See SELECT for further information on %FOREACH. The col-list cannot contain a stream field.
%AFTERHAVING Optional — Applies the condition found in the HAVING clause.
Description
The COUNT aggregate function has two forms:
COUNT can be used in a SELECT query or subquery that references either a table or a view. COUNT can appear in a SELECT list or HAVING clause alongside ordinary field values.
COUNT cannot be used in a WHERE clause. COUNT cannot be used in the ON clause of a JOIN, unless the SELECT is a subquery.
COUNT(expression) like all aggregate functions, can take an optional DISTINCT clause. The DISTINCT clause counts only those columns having distinct (unique) values. COUNT DISTINCT does not count NULL as a distinct value. COUNT(DISTINCT BY(col2) col1) counts col1 values for distinct col2 values; however, the distinct col2 values may include a single NULL as a distinct value.
The ALL keyword counts all non-NULL values, including all duplicates. ALL is the default behavior if no keyword is specified.
No Rows Returned
If no rows are selected, COUNT either returns 0 or NULL, depending on the query:
Stream Fields
You can use COUNT(expression) to count stream field values, with some restrictions. COUNT(streamfield) counts all non-NULL values. It does not check for duplicate values.
You cannot specify the COUNT function’s DISTINCT keyword when expression is a stream field. Attempting to use a DISTINCT keyword with a stream field results in an SQLCODE -37 error.
You cannot specify a stream field in a %FOREACH col-list. Attempting to do so results in an SQLCODE -37 error.
The following example shows valid uses of the COUNT function, where Title is a string field and Notes and Picture are stream fields:
SELECT DISTINCT Title,COUNT(Notes),COUNT(Picture %FOREACH(Title))
FROM Sample.Employee
 
The following examples are not valid when Title is a string field and Notes and Picture are stream fields:
-- Invalid: DISTINCT keyword with stream field
SELECT Title,COUNT(DISTINCT Notes) FROM Sample.Employee
-- Invalid: %FOREACH col-list contains stream field
SELECT Title,COUNT(Notes %FOREACH(Picture))
FROM Sample.Employee
Privileges
To use COUNT(*) you must have table-level SELECT privilege for the specified table. To use COUNT(column-name) you must have column-level SELECT privilege for the specified column, or table-level SELECT privilege for the specified table. You can determine if the current user has SELECT privilege by invoking the %CHECKPRIV command. You can determine if a specified user has table-level SELECT privilege by invoking the $SYSTEM.SQL.CheckPriv() method. For privilege assignment, refer to the GRANT command.
Performance
For optimal COUNT performance, you should define indices as follows:
Changes Made by Uncommitted Transactions
Like all aggregate functions, COUNT always returns the current state of the data, including uncommitted changes, regardless of the current transaction's isolation level, as follows:
For further details, refer to SET TRANSACTION and START TRANSACTION.
Examples
The following example returns the total number of rows in Sample.Person:
SELECT COUNT(*) AS TotalPersons
     FROM Sample.Person
 
The following example returns the count of names, spouses, and favorite colors in Sample.Person. These counts differ because some Spouse and FavoriteColors fields have NULL; COUNT does not count nulls:
SELECT COUNT(Name) AS People,
       COUNT(Spouse) AS PeopleWithSpouses,
       COUNT(FavoriteColors) AS PeopleWithColorPref
FROM Sample.Person
 
The following example returns three values: the total number of rows, the total number of non-NULL values in the FavoriteColors field, and the total number of distinct non-NULL values in the FavoriteColors field:
SELECT COUNT(*) As TotalPersons,
       COUNT(FavoriteColors) AS WithColorPref,
       COUNT(DISTINCT FavoriteColors) AS ColorPrefs
       FROM Sample.Person
 
The following example uses COUNT DISTINCT to return the count of distinct FavoriteColors values in Sample.Person. (FavoriteColors contains several data values and multiple NULLs.) This example also uses the DISTINCT clause to return one row for each distinct FavoriteColors value. The row count is one larger than the COUNT(DISTINCT FavoriteColors) count, because DISTINCT returns a row for a single NULL as a distinct value, but COUNT DISTINCT does not count NULL. The COUNT(DISTINCT BY(FavoriteColors) %ID) value is the same as the row count, because the BY clause does count a single NULL as a distinct value:
SELECT DISTINCT FavoriteColors,
       COUNT(DISTINCT FavoriteColors) AS DistColors,
       COUNT(DISTINCT BY(FavoriteColors) %ID) AS DistColorPeople
FROM Sample.Person
 
The following example use GROUP BY to return a row for each FavoriteColors value, including a row for NULL. Associated with each row are two counts. The first counts the number or records with that FavoriteColors option; records with NULL are not counted. The second counts the number of names associated with each FavoriteColor choice; since Name does not include NULL values, this enables a count of FavoriteColors with NULL:
SELECT FavoriteColors,
       COUNT(FavoriteColors) AS ColorPreference,
       COUNT(Name) AS People
       FROM Sample.Person
       GROUP BY FavoriteColors
 
The following example returns the count of person records for each Home_State value in Sample.Person:
SELECT Home_State, COUNT(*) AS AllPersons
     FROM Sample.Person
     GROUP BY Home_State
 
The following example uses %AFTERHAVING to return the count of person records and the count of persons over 65 for each state in which there is at least one person over 65:
SELECT Home_State, COUNT(Name) AS AllPersons,
     COUNT(Name %AFTERHAVING) AS Seniors
     FROM Sample.Person
     GROUP BY Home_State
     HAVING Age > 65
     ORDER BY Home_State
 
The following example uses both the %FOREACH and the %AFTERHAVING keywords. It returns a row for those states containing people whose names start with “A”, “M”, or “W” (HAVING clause and GROUP BY clause). Each state row contains the following values:
SELECT Home_State,
       COUNT(Name) AS NameCount,
       COUNT(Name %FOREACH(Home_State)) AS StateNameCount,
       COUNT(Name %AFTERHAVING) AS NameCountHaving,
       COUNT(Name %FOREACH(Home_State) %AFTERHAVING) AS StateNameCountHaving
FROM Sample.Person
GROUP BY Home_State
HAVING Name LIKE 'A%' OR Name LIKE 'M%' OR Name LIKE 'W%'
ORDER BY Home_State
 
The following example shows COUNT with a concatenation expression. It returns the total number of non-NULL values in the FavoriteColors field, and the total number of non-NULL values in FavoriteColors concatenated with two other fields, using the concatenate operator (||):
SELECT COUNT(FavoriteColors) AS Color,
       COUNT(FavoriteColors||Home_State) AS ColorState,
       COUNT(FavoriteColors||Spouse) AS ColorSpouse
       FROM Sample.Person
 
When two fields are concatenated, COUNT counts only those rows in which neither field has a NULL value. Because every row in Sample.Person has a non-NULL Home_State value, the concatenation FavoriteColors||Home_State returns the same count as FavoriteColors. Because some rows in Sample.Person have a NULL value for Spouse, the concatenation FavoriteColors||Spouse returns the count of rows which have non-NULL values for both FavoriteColors and Spouse.
See Also