Books
in black and white
Main menu
Share a book About us Home
Books
Biology Business Chemistry Computers Culture Economics Fiction Games Guide History Management Mathematical Medicine Mental Fitnes Physics Psychology Scince Sport Technics
Ads

Teradata RDBMS forUNIX SQL Reference - NCR

NCR Teradata RDBMS forUNIX SQL Reference - NCR, 1997. - 913 p.
Download (direct link): teradataforunix1997.pdf
Previous << 1 .. 94 95 96 97 98 99 < 100 > 101 102 103 104 105 106 .. 241 >> Next

col_name the name(s) of columns used in the GROUP BY clause in the SELECT statement.
col_pos the numeric position of the column(s) used in the GROUP BY clause.
expr the expression(s) used in the GROUP BY clause. Teradata SQL expressions are described in Chapter 6. col_name, col_pos, and expr can comprise single entries or a list.

The GROUP BY clause creates summary information for a table.

Usage Notes

Each col_name in a GROUP BY clause is the name of a column referenced in the SELECT expression list. A col_pos is a positive integer that represents the numeric position of a column referenced in the SELECT expression list.

When an aggregate operation (SUM, AVERAGE, MAX, MIN, or COUNT) is specified, GROUP BY can be used to return a summary row for each group. Aggregate operators can be used only in the SELECT expression list or in the optional HAVING clause.

All non-aggregate groups in a SELECT expression list must be included in the GROUP BY clause. If an ORDER BY clause is used,

7-30

Teradata RDBMS for UNIX SQL Reference
Queries: The SELECT Statement

GROUP BY Clause

Examples

Example 1

any group contained in the ORDER BY clause must also be included in the GROUP BY clause.

The use of col_pos or expr in GROUP BY is non-ANSI, and will be flagged when the SQL flagger is enabled.

The following examples illustrate the use of the GROUP BY clause.

If the following statement was used to generate a report of salary totals by department:

SELECT DeptNo, SUM(Salary) FROM Employee GROUP BY DeptNo;

the result returned would be:

DeptNo Sum(Salary)

100 180,500.00

300 143,000.00

500 268,000.00

600 146,600.00

700 113,000.00

Example 2

The following statement:

SELECT Employee.DeptNo, Department.DeptName, AVG(Salary)

FROM Employee, Department

WHERE Employee.DeptNo = Department.DeptNo GROUP BY Employee.DeptNo;

returns an error message because a non-aggregate group listed in the SELECT expr-list (Department.DeptName) is not listed in the GROUP BY clause.

The following statement:

Example 3

SELECT Employee.DeptNo, AVG(Salary)

FROM Employee, Department

WHERE Employee.DeptNo = Department.DeptNo ORDER BY Department.DeptName GROUP BY Employee.DeptNo;

returns an error message because the non-aggregate group listed in the ORDER BY clause is not listed in the GROUP BY clause.

Teradata RDBMS for UNIX SQL Reference

7-31
Queries: The SELECT Statement

HAVING Clause

HAVING Clause

Function

The HAVING clause of the SELECT statement specifies a conditional expression that must be satisfied for a group of rows to be included in the result data. The condition may define one or more aggregates (MAX, MIN, AVG, SUM, COUNT), and may be applied to the rows of:

A single group defined in the SELECT expression list, which has only aggregate results

One or more groups defined in a GROUP BY clause

Syntax

HAVING cond where: FF06A016
Syntax Element . . . Specifies . . .
HAVING the conditional clause in the SELECT statement.
cond one or more conditional expressions that must be satisfied by the result rows. Aggregate operators may be used with HAVING. HAVING cond selects rows from a single group defined in the SELECT expression list that has only aggregate results, or it selects rows from the group or groups defined in a GROUP BY clause.

Usage Notes

When the WHERE, GROUP BY, and HAVING clauses are used together in a SELECT statement, the order of evaluation is:

1 WHERE

2 GROUP BY

3 HAVING

HAVING results in an error message when the tables referenced in the HAVING clause meet any of the following criteria:

Not declared in the FROM clause

Do not appear in the SELECT expression list

Do not appear in a non-aggregate condition.

7-32

Teradata RDBMS for UNIX SQL Reference
Queries: The SELECT Statement

HAVING Clause

The following examples illustrate the use of the HAVING clause.

Examples

The following statement can be used to display salary ranges for specified departments whose salaries average more than $37,000:

SELECT DeptNo, MIN(Salary), MAX(Salary), AVG(Salary)

FROM Employee

WHERE DeptNo IN (100,300,500,600)

GROUP BY DeptNo

HAVING AVERAGE(Salary) > 37000;

The result is:

DeptNo Minimum(Salary) Maximum(Salary) Average(Salary)

300 23,000.00 65,000.00 47,666.67

500 22,000.00 56,000.00 38,285.71

The following example demonstrates the use of HAVING as applied to the aggregate results of a single group defined in the SELECT expression list. This application is particularly useful in a SELECT subquery.

SELECT COUNT(Employee)

FROM Department WHERE DeptNo = 100 HAVING COUNT(Employee) > 10 ;

Other examples of legitimate use of the HAVING clause:

SELECT SUM(t.a) from t,u HAVING SUM(t.a)=SUM(u.a);

SELECT SUM(t.a), SUM(u.a) from t,u HAVING SUM(t.b)=SUM(u.b); SELECT SUM(t.a) from t,u HAVING SUM(t.b)=SUM(u.b) and u.b = 1 GROUP BY u.b;

_ _ _ Teradata SQL supports using the HAVING clause when referencing
Previous << 1 .. 94 95 96 97 98 99 < 100 > 101 102 103 104 105 106 .. 241 >> Next