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

SQL Data Manipulation Language

Deployment
VantageCloud
VantageCore
Edition
Enterprise
IntelliFlex
VMware
Product
Analytics Database
Teradata Vantage
Release Number
17.20
Published
June 2022
Language
English (United States)
Last Update
2024-04-05
dita:mapPath
pon1628111750298.ditamap
dita:ditavalPath
qkf1628213546010.ditaval
dita:id
esx1472246586715
lifecycle
latest
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