WITH Clause
Purpose
Specifies summary lines and breaks (also known as grouping conditions) that determine how selected results are returned.
The typical use of a WITH clause is with subtotals.
Note: The WITH clause has a different function than the WITH statement modifier. See “WITH Statement Modifier” on page 60.
Syntax
where:
Syntax Element … |
Specifies … |
WITH |
the keyword introducing a condition to be fulfilled by a SELECT statement. |
expression_1 |
a summary line, such as a total, for the values in an expression of the select result. expression_1 can contain one or more aggregate and arithmetic operators that are applied to column values. expression_1 cannot contain LOB columns. |
BY expression_2 |
one or more result expressions for which expression_1 is provided. BY is valid only when specified with WITH. You should consider expression_2 to be a group condition. expression_2 can refer to an expression in the select expression list either by name or by means of a constant that specifies the numeric position of the expression in the expression list. expression_2 cannot specify LOB columns. |
ASC |
the sort order. ASC specifies ascending order and is the default. DESC specifies descending order. If a sort option is not given, result values are sorted in ascending order according to the current session collation. |
ANSI Compliance
The WITH clause is a Teradata extension to the ANSI SQL:2011 standard.
Rules and Restrictions for the WITH Clause
The rules and restrictions for the use of the WITH clause are:
Note that you cannot specify a scalar subquery as the argument of a WITH clause because only aggregate expressions are valid as WITH clause arguments. You can, however, specify a scalar subquery as an argument of an aggregate expression in a WITH clause.
See “Example 2: Specifying Multiple WITH Clauses in a Single SELECT Statement” on page 240.
WITH and ORDER BY clauses in the same SELECT statement function together as follows:
This is true regardless of the structure of the query or the number of WITH clauses.
See “Example 3: Combining WITH and ORDER BY Clauses” on page 241.
Specifying WITH and GROUP BY clauses in the same SELECT statement can produce unintended results.
See “Example 3: Combining WITH and ORDER BY Clauses” on page 241.
Also see “GROUP BY Clause” on page 145.
BY dept_no
specifies a summary for each value in the dept_no column; a summary line is generated following a listing of the values for each department
number.
If you do not specify a BY phrase, the summary line applies to the entire result as specified by the SELECT expression list.
Like the ORDER BY clause, the values of any expression specified by expression_2 can be sorted in either ascending or descending order. For example:
WITH SUM(salary) BY divno ASC, dept_no DESC
Likewise, expression_2 can specify a constant that references an expression by its position in the SELECT expression list. For example:
WITH SUM(salary) BY 2 ASC, 3 DESC
However, an expression that is specified in expression_1 or expression_2 need not be specified in the SELECT expression list.
You can specify the following expressions in a WITH clause.
An aggregate operator must be specified directly before each column to which the operator applies, for example, WITH SUM(salary) or MAX(yrsexp).
WITH dept_no, SUM(salary) BY dept_no
.
You cannot specify expressions that include LOB or UDT columns in a WITH clause.
Title is relevant only for FieldMode output for report generation and normally done only via BTEQ.
This clause lists the title Subtotal at each summary row:
WITH SUM(salary)(TITLE 'Subtotal')
This clause specifies a blank title:
WITH SUM(salary)(TITLE ' ')
Example : Specifying a Detail Line For Each Employee and a Subtotal Line For Each Department
You can use the following statement to generate a departmental salary report that contains a detail line for each employee and a subtotal line for each department:
SELECT name, dept_no, salary
FROM employee
WITH SUM(salary) BY dept_no;
The result returned is as follows:
name dept_no 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
Example : Specifying Multiple WITH Clauses in a Single SELECT Statement
The following statement generates a report of employee salaries 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, dep_tno, salary
FROM employee
WITH SUM(salary) BY dept_no
WITH SUM(salary);
The result returned is as follows:
name dept_no 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,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
Example : Combining WITH and ORDER BY Clauses
Both of the following statements use an ORDER BY clause to sort employee names in ascending order in each dept_no group.
SELECT name, dept_no, salary
FROM employee
ORDER BY name
WITH SUM(salary) BY dept_no
WITH SUM(salary);
SELECT name, dept_no, salary
FROM employee
WITH SUM(salary) BY dept_no
WITH SUM(salary)
ORDER BY name;
The result returned is as follows.
name dept_no salary
‑‑‑‑‑‑‑‑‑‑ ‑‑‑‑‑‑‑ ‑‑‑‑‑‑‑‑‑‑-
Chin M 100 38,000.00
Greene W 100 32,500.00
Jones M 100 50,000.00
Moffit H 100 35,000.00
Peterson J 100 25,000.00
‑‑‑‑‑‑‑‑‑‑
Sum(Salary) 180,500.00
. . .
. . .
. . .
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 or ORDER BY can be unexpected, depending on whether the CASESPECIFIC option was defined on the column and the collation in effect for the session. See “ORDER BY Clause” on page 221 and SQL Data Definition Language.
You must code your statements carefully to avoid returning unintended result sets from combining GROUP BY and WITH clauses. The next example illustrates a possible problem with careless coding of combined GROUP BY and WITH clauses.
The following statement is intended to generate a report of salary totals by dept_no with a grand total of employee salaries:
SELECT dept_no, SUM(salary)
FROM employee
GROUP BY dept_no
WITH SUM(salary);
The result returned is as follows:
dept_no 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. Note that the summary title is truncated because of the narrow width of the dept_no column.
If a WITH clause contains an unnecessary BY phrase, then a useless summary line is generated following each department salary total as seen in the report following this example query:
SELECT dept_no, SUM(salary)
FROM employee
GROUP BY dept_no
WITH SUM(salary) BY dept_no;
dept_no Sum(Salary)
‑‑‑-‑‑‑ ‑‑‑‑‑‑‑‑‑‑
100 180,500.00
‑‑‑‑‑‑‑‑‑‑
Sum(Sa 180,500.00
.
.
.
700 113,000.00
‑‑‑‑‑‑‑‑‑‑‑
Sum(Sa 113,000.00
Example : Scalar Subquery in the WITH … BY Clause of a SELECT Statement
The following example specifies a scalar subquery (SELECT prod_name…
) in its BY clause.
SELECT SUM(amount)
FROM sales_table AS s
WITH AVG(amount) BY (SELECT prod_name
FROM prod_table AS p
WHERE p.prod_no = s.prod_no);