COUNT
Purpose
Returns a column value that is the total number of qualified rows in value_expression.
Syntax
where:
|
Syntax element … |
Specifies … |
|
ALL |
that all non-null values of value_expression, including duplicates, are included in the total count. This is the default. |
|
DISTINCT |
that a value_expression that evaluates to NULL or to a duplicate value does not contribute to the total count. |
|
value_expression |
a literal or column expression for which the total count is computed. The expression cannot contain any ordered analytical or aggregate functions. |
|
* |
to count all rows in the group of rows on which COUNT operates. |
Usage Notes
|
This syntax … |
Counts the total number of rows … |
|
COUNT(value_expression) |
in the group for which value_expression is not null. |
|
COUNT (DISTINCT value_expression) |
in the group for which value_expression is unique and not null. |
|
COUNT(*) |
in the group of rows on which COUNT operates. |
For COUNT functions that return the group, cumulative, or moving count, see “Window Aggregate Functions” on page 984.
COUNT is valid for any data type.
With the exception of COUNT(*), the computation does not include nulls. For more information, see SQL Fundamentals and “Aggregates and Nulls” on page 31.
For an example that uses COUNT(*) and nulls, see “Example 2” on page 42.
Result Type and Attributes
The following table lists the data type and format for the result of COUNT.
|
Mode |
Data Type and Format |
|
ANSI |
If MaxDecimal in DBSControl is… |
|
Teradata |
INTEGER and the format is the default format for INTEGER. |
For information on data type default formats, see “Data Type Formats and Format Phrases” in SQL Data Types and Literals.
ANSI mode uses DECIMAL because tables frequently have a cardinality exceeding the range of INTEGER.
Teradata mode uses INTEGER to avoid regression problems.
When in Teradata mode, if the result of COUNT overflows and reports an error, you can cast the result to another data type, as illustrated by the following example.
SELECT CAST(COUNT(*) AS BIGINT)
FROM BIGTABLE;
The following table lists the default title for the result of COUNT.
|
Operation |
Title |
|
COUNT(x) |
Count(x) |
|
COUNT(*) |
Count(*) |
Example
COUNT(*) reports the number of employees in each department because the GROUP BY clause groups results by department number.
SELECT DeptNo, COUNT(*) FROM Employee
GROUP BY DeptNo
ORDER BY DeptNo;
Without the GROUP BY clause, only the total number of employees represented in the Employee table is reported:
SELECT COUNT(*) FROM Employee;
Note that without the GROUP BY clause, the select list cannot include the DeptNo column because it returns any number of values and COUNT(*) returns only one value.
Example
If any employees have been inserted but not yet assigned to a department, the return includes them as nulls in the DeptNo column.
SELECT DeptNo, COUNT(*) FROM Employee
GROUP BY DeptNo
ORDER BY DeptNo;
Assuming that two new employees are unassigned, the results table is:
DeptNo Count(*)
------ --------
? 2
100 4
300 3
500 7
600 4
700 3
Example
If you ran the report in Example 2 using SELECT... COUNT … without grouping the results by department number, the results table would have only registered non-null occurrences of DeptNo and would not have included the two employees not yet assigned to a department(nulls). The counts differ (23 in Example 2 as opposed to 21 using the statement documented in this example).
Recall that in addition to the 21 employees in the Employee table who are assigned to a department, there are two new employees who are not yet assigned to a department (the row for each new employee has a null department number).
SELECT COUNT(deptno) FROM employee ;
The result of this SELECT is that COUNT returns a total of the non-null occurrences of department number.
Because aggregate functions ignore nulls, the two new employees are not reflected in the figure.
Count(DeptNo)
--------------
21
Example
This example uses COUNT to provide the number of male employees in the Employee table of the database.
SELECT COUNT(sex)
FROM Employee
WHERE sex = 'M' ;
The result is as follows.
Count(Sex)
----------
12
Example
In this example COUNT provides, for each department, a total of the rows that have non-null department numbers.
SELECT deptno, COUNT(deptno)
FROM employee
GROUP BY deptno
ORDER BY deptno ;
Notice once again that the two new employees are not included in the count.
DeptNo Count(DeptNo)
------ -------------
100 4
300 3
500 7
600 4
700 3
Example
To get the number of employees by department, use COUNT(*) with GROUP BY and ORDER BY clauses.
SELECT deptno, COUNT(*)
FROM employee
GROUP BY deptno
ORDER BY deptno ;
In this case, the nulls are included, indicated by QUESTION MARK.
DeptNo Count(*)
------ --------
? 2
100 4
300 3
500 7
600 4
700 3
Example
To determine the number of departments in the Employee table, use COUNT (DISTINCT) as illustrated in the following SELECT COUNT.
SELECT COUNT (DISTINCT DeptNo)
FROM Employee ;
The system responds with the following report.
Count(Distinct(DeptNo))
-----------------------
5