Derived Table Examples | SQL SELECT Statements | Teradata Vantage - 17.05 - Example: Using Derived Tables for Multilevel Aggregation - Advanced SQL Engine - Teradata Database

Teradata Vantage™ - SQL Data Manipulation Language

Product
Advanced SQL Engine
Teradata Database
Release Number
17.05
Release Date
January 2021
Content Type
Programming Reference
Publication ID
B035-1146-175K
Language
English (United States)

You cannot specify aggregates in a WHERE clause predicate. However, derived tables make such calculations easy to perform.

The following example shows how derived tables facilitate this.

In this example, the WHERE condition compares values from rows in the base table employee with the (in this case single) values of the column average_salary in the derived table workers.

     SELECT name, salary, average_salary
     FROM (SELECT AVG(salary)
           FROM employee) AS workers (average_salary), employee
     WHERE salary > average_salary
     ORDER BY salary DESC;

The query returns this answer set:

name salary average_salary
Russel S 55,000.00 38,147.62
Watson L 56,000.00 38,147.62
Phan A 55,000.00 38,147.62
Aguilar J 45,000.00 38,147.62
Carter J 44,000.00 38,147.62

The following example is more elaborate, grouping the same information by dept_no. In this example, the statement returns all salaries that exceed the respective department averages.

In this example, the derived table is a grouped table, providing a set of rows. The outer WHERE clause has an equality join between the department number (dept_no) of the base table, employee, and the derived table.

     SELECT name, salary, dept_no, average_salary
     FROM (SELECT AVG(salary), dept_no
           FROM employee
           GROUP BY dept_no) AS workers (average_salary,dept_num),
                               employee
     WHERE salary > average_salary
     AND   dept_num = dept_no
     ORDER BY dept_no, salary DESC;

The answer set might look like this:

name salary dept_no average_salary
Chin M 38,000.00 100 32,625.00
Moffit H 35,000.00 100 32,625,00
Russel S 55,000.00 300 47,666.67
Phan A 55,000.00 300 47,666.67
Watson L 56,000.00 500 38,285.71
Carter J 44,000.00 500 38,385.71
Smith T 42,000.00 500 38,285.71
Aguilar J 45,000.00 600 36,650.00