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 .. 93 94 95 96 97 98 < 99 > 100 101 102 103 104 105 .. 241 >> Next


7-25
Queries: The SELECT Statement

WITH Clause

The following statement can be used to generate a departmental Example salary report that contains a detail line for each employee and a

subtotal line for each department:

SELECT Name, DeptNo, Salary FROM Employee WITH SUM(Salary) BY DeptNo;

The result returned is:

Name DeptNo Salary
Peterson J 100 25,000.00
Moffit H 100 35,000.00
Jones M 100 50,000.00
Chin M 100 38,000.00
Greene W 100 32,500.00
Sum(Salary) 180,500.00
Leidner P 300 34,000.00
Phan A 300 55,000.00
Russell S 300 65,000.00

Sum(Salary)154,000.00

More than one WITH clause can be used in a SELECT statement to Muldple wIth Qauses specify different kinds of summaries. Each succeeding WITH clause

refers to an ever-broader grouping of rows.

• The BY phrase in the first-specified WITH clause defines the least important sort key.

• The BY phrase in the next-specified WITH clause defines the next-to-least important sort key.

• The final WITH clause defines the major sort key.

The following statement generates a report of employee salaries Example ordered by department, with a summary line of total salaries for

each department and a final summary line of total salaries for the entire organization:

SELECT Name, DeptNo, Salary FROM Employee WITH SUM(Salary) BY DeptNo WITH SUM(Salary);

7-26

Teradata RDBMS for UNIX SQL Reference
Queries: The SELECT Statement

WITH Clause

Combined WITH and ORDER BY Clauses

Example

The result returned is:

Name DeptNo

Peterson J Moffit H Jones M Chin M Greene W

100

100

100

100

100

Salary

25.000.00

35.000.00

50.000.00

38.000.00

32.000.00

Sum(Salary) 180,000.00

Leidner P 300 34, 000.00
Phan A 300 55, 000.00
Russell S 300 65, 000.00
Sum(Salary) 154, 000.00
Smith T 500 42 ,000.00
Clements D 700 38, 000.00
Sum(Salary) 113 ,000.00

Sum(Salary) 851,000.00

An ORDER BY clause can be specified before or after any WITH clause. See also the “ORDER BY Clause” following.

When WITH and ORDER BY are used together, the WITH clause defines the major sort key and the ORDER BY clause defines the minor sort key. This is true regardless of the structure of the query or the number of WITH clauses.

Both of the following statements use an ORDER BY clause to sort employee names in ascending order in each department group:

SELECT Name, DeptNo, Salary FROM Employee ORDER BY Name

WITH SUM(Salary) BY DeptNo WITH SUM(Salary);

SELECT Name, DeptNo, Salary FROM Employee WITH SUM(Salary) BY DeptNo WITH SUM(Salary)

ORDER BY Name;

The result returned is:

Name DeptNo

Salary

Chin M Greene W Jones M Moffit H Peterson J

100

100

100

100

100

38.000.00 32,500.00

50.000.00

35.000.00

25.000.00

Sum(Salary) 180,500.00

Teradata RDBMS for UNIX SQL Reference

7-27
Queries: The SELECT Statement

WITH Clause

Brangle B 700 30,000.00

Clements D 700 38,000.00

Smith T 700 45,000.00

Sum(Salary)113,000.00

Sum(Salary) 851,100.00

If any sort key column contains character data that was entered in mixed case, the results produced from WITH...BY and/or ORDER BY may be unexpected, depending on whether the CASESPECIFIC option was defined on the column and the collation in effect for the session (see the “ORDER BY Clause” earlier in this section, and the SET SESSION COLLATION statement).

Using WITH and GROUP BY clauses in the same SELECT statement C°mbined WITH and may produce unintended results. See also the “GROUP BY Clause”

GROUP BY Clauses 7 in a following section.

The following statement is intended to generate a report of salary Example 7 totals by department with a grand total of employee salaries:

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

The result returned is:

DeptNo Sum(Salary)
100 180 ,500.00
300 143 ,000.00
500 268 ,000.00
600 146 ,600.00
700 113 ,000.00
Sum(Sa 851 ,100.00

As would be expected, the WITH clause produces a summary line of total salaries for all departments. However, the summary title is truncated because of the narrow width of the DeptNo column.

7-28

Teradata RDBMS for UNIX SQL Reference
Queries: The SELECT Statement

WITH Clause

If the WITH clause contains an unnecessary BY phrase:

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

a useless summary line is generated following each department salary total:

DeptNo Sum(Salary)
100 180,500.00
Sum(Sa 180,500.00
700 113,000.00

Sum(Sa 113,000.00

Teradata RDBMS for UNIX SQL Reference

7-29
Queries: The SELECT Statement

GROUP BY Clause

GROUP BY Clause

Function

The GROUP BY clause of the SELECT statement groups result rows by the values in one or more columns.

Syntax

-GROUP BY-

-col_name-

- co_pos —

— expr -

FF06A015

where:
Syntax Element . . . Specifies . . .
GROUP BY a reference to one or more expressions in the select expression list.
Previous << 1 .. 93 94 95 96 97 98 < 99 > 100 101 102 103 104 105 .. 241 >> Next