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 |