PolarDB:Aggregate functions

Examples

The following examples are all based on the sample database that is described in A sample database.

• Execute the following statement to find the highest and lowest salaries:

  SELECT MAX(sal) highest_salary, MIN(sal) lowest_salary FROM emp;

  The following result is returned:

   highest_salary | lowest_salary 
  ----------------+---------------
             5000 |           800
  (1 row)

• Execute the following statement to find the employee with the highest salary:

  SELECT ename FROM emp WHERE sal = MAX(sal);

  The following result is returned:

  ERROR:  aggregate functions are not allowed in WHERE

  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);

  The following result is returned:

   ename 
  -------
   KING
  (1 row)

• The subquery is an independent computation that obtains its own result separately from the outer query.

  • Use the GROUP BY clause. For example, execute the following statement to query the highest salary in each department:

    SELECT deptno, MAX(sal) FROM emp GROUP BY deptno;

    The following result is returned:

     deptno |   max
    --------+---------
         10 | 5000.00
         20 | 3000.00
         30 | 2850.00
    (3 rows)

  • Use with the WHERE and HAVING clause.

    • Use the HAVING clause to filter these grouped rows and produce one output row per department. Each aggregate result is computed over the rows matching that department.

      SELECT deptno, MAX(sal) FROM emp GROUP BY deptno HAVING AVG(sal) > 2000;

      The following result is returned. The same result is returned for only those departments that have an average salary greater than 2000.

    • Use the WHERE clause to query 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;

      The following result is returned:

       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.