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

SQL Data Manipulation Language

Product
Advanced SQL Engine
Teradata Database
Release Number
17.00
Published
September 2020
Language
English (United States)
Last Update
2021-01-23
dita:mapPath
qtb1554762060450.ditamap
dita:ditavalPath
lze1555437562152.ditaval
dita:id
B035-1146
lifecycle
previous
Product Category
Teradata Vantage™

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