COUNT (SQL)

Synopsis

COUNT(*)
COUNT(expression)

COUNT(DISTINCT expression)
COUNT(DISTINCT BY(column) expression)
COUNT(ALL expression)

COUNT( ... expression %FOREACH(column))
COUNT( ... expression ... %AFTERHAVING)

Description

COUNT is an aggregate function that returns a count of the number of rows in a table or column. COUNT returns the BIGINT data type. If the count includes no rows, COUNT returns 0 or NULL, depending on the query. For more details, see No Rows Returned in Count.

Use COUNT in a SELECT query to count rows from the table referenced in the query and return the count in the result set. You can also use COUNT in a subquery that references either a table or view and in the HAVING clause. You cannot use COUNT in a WHERE clause. Unless SELECT is a subquery, you also cannot use COUNT in the ON clause of a JOIN.

• COUNT(*) returns the number of rows in the table or view. COUNT(*) counts all rows, including ones that contain duplicate column values or NULL values.

  This query returns the total number of rows in Sample.Person.

  SELECT COUNT(*) FROM Sample.Person

  

  Example: Count Table Rows and Column Values

• COUNT(expression) returns the number of values in expression, which is a table column name or an expression that evaluates to a column of data. COUNT(expression) does not count NULL values.

  This query returns the number of non-NULL values in the Name column of Sample.Person.

  SELECT COUNT(Name) AS TotalNames FROM Sample.Person

  

  Examples:

  • Count Table Rows and Column Values

  • Count Stream Column Values

  • Count Non-NULL Values in Combination of Columns

• COUNT(DISTINCT expression) uses a DISTINCT clause to return the count of the distinct (unique) values in the expression column. You cannot use DISTINCT with stream columns. What is counted as a distinct value depends on the column’s collation. For example, with the default column collation of %SQLUPPER, values that differ in letter case are not counted as distinct values. To count every letter-case variant as a distinct value, use COUNT(DISTINCT(%EXACT(expression))). NULL values are not included in COUNT DISTINCT counts.

  This statement returns the count of unique ages in Sample.Person.

  SELECT COUNT(DISTINCT Age) FROM Sample.Person

  

  Example: Count Distinct Column Values

• COUNT(DISTINCT BY(column) expression) filters out rows that contain duplicates in the column list specified by column, and then returns a count of values in the expression column. If column contains a NULL value, the NULL is counted as a distinct value.

  This statement returns a count of FavoriteColors values for people with distinct (unique) names.

  SELECT COUNT(DISTINCT BY(Name) FavoriteColors) FROM Sample.Person

  

  Example: Count Distinct Column Values

• COUNT(ALL expression) returns the count of all values for expression. The ALL keyword counts all non-NULL values, including all duplicates. ALL is the default behavior if no keyword is specified.

• COUNT( ... expression %FOREACH(column)) groups the values in the expression column by the distinct values contained in the column list and returns the count of each group. %FOREACH and GROUP BY are similar. While GROUP BY operates on an entire query, %FOREACH allows selection of aggregates on sub-populations without restricting the entire query population.

  This query returns a row for each person specified in Sample.Person, where each row contains that person’s name, home state, and a count of the names of people who also live in that state.

  SELECT
    Name,
    Home_State,
    COUNT(Name %FOREACH(Home_State)) AS PersonCountInState
  FROM Sample.Person

  

  Example: Count Grouped Values

• COUNT( ... expression ... %AFTERHAVING) counts the rows in expression only after applying the condition found in the HAVING clause. If you omit %AFTERHAVING, the query does not account for the HAVING condition in its count.

  This query returns the count of names grouped by state and the count of names that start with "M" grouped by state.

  SELECT
    Home_State,
    COUNT(Name) AS NameCount,
    COUNT(Name %AFTERHAVING) AS MNameCount
  FROM Sample.Person
  GROUP BY Home_State
  HAVING Name LIKE 'M%'

  

  Example: Count Grouped Values

Arguments

Examples

Count Table Rows and Column Values

This query returns the total number of rows in the Sample.Person table. The count includes rows containing NULL values in one or more columns.

SELECT COUNT(*) AS TotalPersons
FROM Sample.Person

This query returns the count of names, spouses, and favorite colors in Sample.Person. COUNT does not include NULL values in column counts. Therefore, the number of return values for each column might differ or be less than the total number of rows returned by COUNT(*).

SELECT
  COUNT(Name) AS People,
  COUNT(Spouse) AS PeopleWithSpouses,
  COUNT(FavoriteColors) AS PeopleWithColorPref
FROM Sample.Person

Count Distinct Column Values

This query uses COUNT DISTINCT to return the count of distinct FavoriteColors values in Sample.Person. The FavoriteColors column contains several data values and multiple NULL. This query also uses the SELECT DISTINCT clause to return one row for each distinct FavoriteColors value. The row count is one larger than the COUNT(DISTINCT FavoriteColors) count. 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

Count Grouped Values

The queries in this example use GROUP BY to group repeated values in a column, returning one row per unique value. The queries then use COUNT to return a per-group count of values from a different column.

This query returns one row per distinct FavoriteColors value. Assuming that FavoriteColors is not required, the query returns a row for NULL values (if any). Associated with each row are two counts:

• The number of rows that have that FavoriteColors option. Rows with NULL values are not counted.

• The number of names associated with each FavoriteColors option. Assuming that Name does not include NULL values, this count includes a count of NULL values in FavoriteColors.

SELECT
  FavoriteColors,
  COUNT(FavoriteColors) AS ColorPreference,
  COUNT(Name) AS People
FROM Sample.Person
GROUP BY FavoriteColors

This query returns the count of person rows for each Home_State value in Sample.Person.

SELECT
  Home_State,
  COUNT(*) AS AllPersons
FROM Sample.Person
GROUP BY Home_State

This query uses %AFTERHAVING to return the count of person rows, and the count of persons over 65, for each state with 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

This query uses both the %FOREACH and %AFTERHAVING keywords. It uses GROUP BY to return one row per state and HAVING to filter only on people whose names start with "A", "M", or "W".

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

Each state row contains these counts:

• COUNT(Name) — All people in the database. Assuming Name is required, this count is the same for all rows.

• COUNT(Name %FOREACH(Home_State)) — All people in the state.

• COUNT(Name %AFTERHAVING) — All people in the database that meet the HAVING condition. Assuming Name is required, this number is the same for all rows.

• COUNT(Name %FOREACH(Home_State) %AFTERHAVING): All people in the state that meet the HAVING condition.

Count Non-NULL Values in Combination of Columns

This query uses COUNT with a concatenation operator (||) to count the rows in which both the FavoriteColors and Home_State columns do not contain NULL values.

SELECT COUNT(FavoriteColors||Home_State) AS ColorState
FROM Sample.Person

Count Stream Column Values

You can use COUNT(expression) to count stream column values, with some restrictions:

• Column counts always include all non-NULL values, including duplicate values.

• You cannot specify a stream field in a COUNT DISTINCT expression clause. Attempting to do so results in an SQLCODE -37 error.

• You cannot specify a stream field in a %FOREACH column clause. Attempting to do so results in an SQLCODE -37 error.

This query shows a valid use 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

These queries containing stream fields are not valid.

-- 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

No Rows Returned in Count

These examples show what COUNT returns when the SELECT query selects no rows for the function to count. Depending on the query, COUNT returns either 0 or NULL.

If the SELECT selectItem list does not contain any references to columns in the FROM clause tables, other than columns supplied to aggregate functions, COUNT returns 0.

COUNT is the only aggregate function that returns 0. All other aggregate functions return NULL. The query returns a %ROWCOUNT of 1. Sample query:

SELECT
  COUNT(*) AS Recs, COUNT(Name) AS People,
  AVG(Age) AS AvgAge, MAX(Age) AS MaxAge,
  CURRENT_TIMESTAMP AS Now
FROM Sample.Employee
WHERE Name %STARTSWITH 'ZZZ'

If the SELECT selectItem list contains any direct reference to a column in a FROM clause table, or if TOP 0 is specified, COUNT returns NULL. The query returns a %ROWCOUNT of 0. Sample query:

SELECT
  COUNT(*) AS Recs,
  COUNT(Name) AS People,
  $LENGTH(Name) AS NameLen
FROM Sample.Employee WHERE Name %STARTSWITH 'ZZZ'

If no table is specified, COUNT(*) returns 1. The query returns a %ROWCOUNT of 1. Sample query:

SELECT COUNT(*) AS Recs

Security and Privileges

To use the COUNT(*) syntax, you must have table-level SELECT privilege for the specified table.

To use COUNT(expression) syntaxes, you must have either column-level SELECT privilege for the column specified by expression or table-level SELECT privilege for the specified table.

• To determine if you have SELECT privilege, use %CHECKPRIV.

• To determine if you have table-level SELECT privilege, use $SYSTEM.SQL.Security.CheckPrivilege()Opens in a new tab.

• To assign privileges, use GRANT.

Performance

To improve COUNT performance, consider defining these indexes:

• For the COUNT(*) syntax, define a bitmap extent index, if this index was not automatically defined when the table was created.

• For COUNT(expression) syntaxes, define a bitslice index for the column specified by expression. Query Plan optimization of COUNT(expression) automatically applies default collation to the column being counted.

Transaction Considerations

Like all aggregate functions, COUNT returns the current state of the data, including uncommitted changes, regardless of the current transaction's isolation level. COUNT follows this behavior:

• Counts inserted and updated records, even if those changes have not been committed and can be rolled back.

• Does not count deleted records, even if those deletions have not been committed and can be rolled back.

For more details, see SET TRANSACTION and START TRANSACTION.

See Also

• Aggregate Functions

• AVG

• SUM