Using Derived Tables To Do Multilevel Aggregation - Teradata Database

Using Derived Tables To Do Multilevel Aggregation

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.

     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

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.

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

     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

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