Example: Using Derived Tables To Do Multilevel Aggregation - Teradata Database - Teradata Vantage NewSQL Engine

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:

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: