15.00 - Defining Summaries (WITH Clause) - Interactive Teradata Query Facility

Interactive Teradata Query User Guide

prodname
Interactive Teradata Query Facility
vrm_release
15.00
category
User Guide
featnum
B035-2452-034K

Defining Summaries (WITH Clause)

To specify summaries of values within a numeric result, you use aggregate operators in a Teradata SQL WITH clause. For example, adding a WITH clause to the original SELECT statement for your report, as follows:

SELECT DeptNo, Name, Salary FROM Employee
WHERE DeptNo IN (100, 700)
WITH SUM(Salary)
ORDER BY Name;

provides a grand total of employee salaries for the two departments.

Including a BY keyword in a WITH clause allows you to specify group subtotals. For example, to display salary subtotals for each department, add another WITH clause to the statement, as follows:

SELECT DeptNo, Name, Salary FROM Employee
WHERE DeptNo IN (100, 700)
WITH SUM(Salary) BY DeptNo
WITH SUM(Salary)
ORDER BY Name;

The result of these WITH clauses is shown in Figure 12. Note that SUM provides the title “SUM(Salary)” for each subtotal and the grand total. A dotted line separates the amounts being summed from the subtotal, and the last subtotal from the grand total. Note also that the clause “WITH SUM(SALARY) BY DEPTNO” has the effect of ordering the result by department number.

Figure 12: WITH Clause Example

You could reorganize this report in descending order of department number using the DESC keyword, for example:

SELECT DeptNo, Name, Salary FROM Employee
WHERE DeptNo IN (100, 700)
WITH SUM(Salary) BY DeptNo DESC
WITH SUM(Salary)
ORDER BY Name;