Similar to most other relational database services, PolarDB for Oracle support aggregate functions. An aggregate function computes a single result from multiple input rows. For example, you can use aggregates to compute the COUNT, SUM, AVG (average), MAX (maximum), and MIN (minimum) over a set of rows.
The following example shows how the highest and lowest salaries are found in a query:
SELECT MAX(sal) highest_salary, MIN(sal) lowest_salary FROM emp; highest_salary | lowest_salary ----------------+--------------- 5000.00 | 800.00 (1 row)
If you want to find the employee with the largest salary, the following query is invalid:
SELECT ename FROM emp WHERE sal = MAX(sal); ERROR: aggregates not allowed in WHERE clause
The MAX aggregate function cannot be used in a WHERE clause. The WHERE clause determines the rows that can be aggregated. The clause must be evaluated before aggregate functions are computed. However, you can use a subquery to restate the query to obtain the expected result:
SELECT ename FROM emp WHERE sal = (SELECT MAX(sal) FROM emp); ename ------- KING (1 row)
The subquery is an independent computation that obtains its own result separately from the outer query.
Aggregates are also very useful in combination with the GROUP BY clause. For example, the following query retrieves the highest salary in each department.
SELECT deptno, MAX(sal) FROM emp GROUP BY deptno; deptno | max --------+--------- 10 | 5000.00 20 | 3000.00 30 | 2850.00 (3 rows)
This query produces one output row per department. Each aggregate result is computed over the rows matching that department. You can use the HAVING clause to filter these grouped rows.
SELECT deptno, MAX(sal) FROM emp GROUP BY deptno HAVING AVG(sal) > 2000; deptno | max --------+--------- 10 | 5000.00 20 | 3000.00 (2 rows)
This query retrieves the same results for only those departments that have an average salary greater than 2000.
The following query takes into account only the highest paid employees who are analysts in each department.
SELECT deptno, MAX(sal) FROM emp WHERE job = 'ANALYST' GROUP BY deptno HAVING AVG(sal) > 2000; deptno | max --------+--------- 20 | 3000.00 (1 row)
A subtle distinction exists between the WHERE and HAVING clauses. Before grouping occurs and aggregate functions are applied, the WHERE clause filters out rows. After rows are grouped and aggregate functions are computed for each group, the HAVING clause applies filters on the results.
Therefore, in the previous example, only employees who are analysts are considered. From this subset, the employees are grouped by department and only those groups where the average salary of analysts in the group is greater than 2000 are in the final result. Only the group for department 20 meets the criteria and the maximum analyst salary in department 20 is 3000.00.