Query data using aggregate functions with GROUP BY and HAVING - PolarDB

PolarDB for PostgreSQL (Compatible with Oracle) supports aggregate functions. An aggregate function computes a single result from multiple input rows — for example, COUNT, SUM, AVG, MAX, and MIN over a set of rows.

How SELECT processes aggregates

Understanding the execution order helps you write correct aggregate queries:

All tables in the FROM clause are evaluated.
The WHERE clause filters individual rows. Because this happens before aggregation, aggregate functions cannot appear in WHERE conditions.
The GROUP BY clause divides the filtered rows into groups. The HAVING clause then filters those groups.

All examples below use the sample database described in A sample database.

Find the highest and lowest values

Use MAX and MIN together to retrieve the salary range in one query:

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

Output:

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

Use a subquery when WHERE needs an aggregate

Aggregate functions are not allowed in the WHERE clause. The following query fails because WHERE is evaluated before aggregates are computed:

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

ERROR:  aggregate functions are not allowed in WHERE

Move the aggregate into a subquery. The subquery runs independently and returns a single value that the outer query can compare against:

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

Output:

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

Group results with GROUP BY

The GROUP BY clause computes one aggregate per group. This query returns the highest salary in each department:

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

Output:

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

Filter groups with HAVING

HAVING filters groups after GROUP BY and the aggregate functions are computed — unlike WHERE, which filters individual rows before grouping. Use HAVING to include only groups that meet an aggregate condition.

Return only departments where the average salary exceeds 2,000:

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

Combine WHERE and HAVING

Use WHERE and HAVING together to apply two levels of filtering:

WHERE narrows the rows before grouping (pre-aggregate filter).
HAVING narrows the groups after aggregation (post-aggregate filter).

The following query finds the highest analyst salary per department, but only for departments where analysts average more than 2,000:

SELECT deptno, MAX(sal)
FROM emp
WHERE job = 'ANALYST'
GROUP BY deptno
HAVING AVG(sal) > 2000;

Output:

 deptno |   max
--------+---------
     20 | 3000.00
(1 row)

Only department 20 qualifies: it has analyst rows (selected by WHERE), and their average salary exceeds 2,000 (filtered by HAVING). The maximum analyst salary in department 20 is 3,000.00.