# Teradata RDBMS forUNIX SQL Reference - NCR

**Download**(direct link)

**:**

**70**> 71 72 73 74 75 76 .. 241 >> Next

The DISTINCT option specifies that duplicate values not be used DISTINCT Opti°n when an expression is processed.

SELECT COUNT(DISTINCT JobTitle) FROM...

returns the number of unique job titles. In a single query, only one expression may be used as the argument for an aggregate operator that uses the DISTINCT option. However, more than one aggregate operator may use DISTINCT with other occurrences of the expression in the same query.

For example:

SELECT SUM(DISTINCT x), AVG(DISTINCT x) FROM...

DISTINCT aggregate operations may be done only at the first level of aggregation.

6-18

Teradata RDBMS for UNIX SQL Reference

SQL Expressions

Aggregate Operators

Table 6-3 lists the attributes for the result of an aggregate operation.

Result Attributes

The SUM operator may result in a numeric overflow or the loss of data because of the default output format. If this occurs, a data type declaration may be used to override the default.

For example, if x comprises many rows of INTEGER values, it may be necessary to specify:

SUM(x(FLOAT))

If x is an integer, AVERAGE does not display a fractional value. A fractional value may be obtained by casting the value as decimal.

For example:

CAST(AVG(value) AS DECIMAL(9,2))

Table 6-3

Default Attributes of Aggregate Results

Operation Data Type Format Result Heading

SUM(x) DECIMAL(15,m) if x is DECIMAL(n,m); Same format as x, if x is DECIMAL(n,m); Sum(x)

SUM(x) INTEGER, if x is BYTEINT or SMALLINT; otherwise, same data type as x. ’-(10)9’ if x is BYTEINT or SMALLINT; otherwise, same format as x Sum(x)

AVG(x) FLOAT Same format as x Average(x)

MIN/MAX(x) Same data type as x Same format as x Minimum(x) or Maximum(x)

COUNT(x) INTEGER/DECIMAL(15,0) ’-(10)9’/’Z(14)9’ Count(x)

COUNT(*) INTEGER/DECIMAL(15,0) ’-(10)9’/’Z(14)9’ Count(*)

The COUNT operator has two forms, COUNT(value) and cOunt Operator COUNT(*). Both forms of COUNT operate on any data type.

The COUNT(value) form counts the total number of non-null occurrences of (value).

The COUNT(*) form counts the total number of rows in each group of a GROUP BY clause, including groups with Null values for (value).

Teradata RDBMS for UNIX SQL Reference

6-19

SQL Expressions

Aggregate Operators

Internally, COUNT uses decimal (18,0).

In this mode . . . The number is cast to . . . With a default display format of . . .

ANSI DECIMAL (15,0) )9' 4) 1 ‘Z(

or

NUMERIC (15,0)

Teradata INTEGER -(10)9

The decimal result has been chosen for ANSI mode because the Teradata RDBMS uses COUNT on tables that have a row count beyond the range of INTEGER.

The INTEGER result is retained for BTET (Teradata) mode to avoid regression problems. When in Teradata mode, if the result of COUNT overflows and an error is reported, the script can be changed to use CAST, for example

SELECT CAST(COUNT(*) AS DECIMAL(15,0) FORMAT 'Z(14)9')

FROM BIGTABLE;

COUNT(*) selects the number of employees in each department:

Example 1

´ SELECT DeptNo, COUNT(*) FROM Employee

GROUP BY DeptNo ORDER BY DeptNo;

If any employees have been inserted but not yet assigned to a department, the return includes a count of the Null values in the DeptNo column.

Assuming that two new employees are unassigned, the return is:

DeptNo Count(*)

? 2

100 4

300 3

500 7

600 4

700 3

However, the form of the COUNT operator shown in this statement only registers non-null occurrences of DeptNo. Therefore, the return does not include unassigned employees.

SELECT COUNT(DeptNo) FROM Employee;

The output is as follows.

Count(DeptNo)

21

6-20

Teradata RDBMS for UNIX SQL Reference

SQL Expressions

Aggregate Operators

Here, COUNT provides the number of male employees in the Example 2 Employee table of the database:

SELECT COUNT(sex) FROM Employee WHERE sex = 'M' ;

The result is as follows.

Count(Sex)

12

In this examples, assume that in addition to the 21 employees in the Example 3 Employee table, there are two new employees who have not yet

been assigned to a department (that is, the row for each new employee has a null department number). As a prefix operator in the following statement:

SELECT COUNT(deptno) FROM employee

COUNT returns a total of the non-null occurrences of department number. Since aggregate operations ignore Nulls, the two new employees are not reflected in the figure:

Count(DeptNo)

21

As a prefix operator in the statement:

Example 4

´ SELECT deptno, COUNT(deptno)

FROM employee GROUP BY deptno ORDER BY deptno ;

COUNT provides for each department a total of the rows that have non-null department numbers. Again, the two new employees are not included in the count.

DeptNo Count(DeptNo)

100 4

300 3

500 7

600 4

700 3

To get the number of employees by department, use COUNT(*):

SELECT deptno, COUNT(*)

FROM employee GROUP BY deptno ORDER BY deptno ;

In this case, the nulls are included, indicated by >?:

**70**> 71 72 73 74 75 76 .. 241 >> Next