15.00 - COUNT - Teradata Database

Teradata Database SQL Functions, Operators, Expressions, and Predicates

Product
Teradata Database
Release Number
15.00
Content Type
Programming Reference
Publication ID
B035-1145-015K
Language
English (United States)
Last Update
2018-09-24

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…

  • 0 or 15, then the result type is DECIMAL(15,0) and the format is -(15)9.
  • 18, then the result type is DECIMAL(18,0) and the format is -(18)9.
  • 38, then the result type is DECIMAL(38,0) and the format is -(38)9.
  • 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