WITH Clause Examples | SQL SELECT Statements | Teradata Vantage - WITH Clause Examples - Advanced SQL Engine - Teradata Database

SQL Data Manipulation Language

Product
Advanced SQL Engine
Teradata Database
Release Number
17.05
Published
January 2021
Language
English (United States)
Last Update
2021-01-22
dita:mapPath
vnq1596660420420.ditamap
dita:ditavalPath
hoy1596145193032.ditaval
dita:id
B035-1146
lifecycle
previous
Product Category
Teradata Vantage™

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 and Teradata Vantage™ - SQL Data Definition Language Detailed Topics , B035-1184 .

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. 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 redundant 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 the 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);