Like most other relational database products,
EnterpriseDB supports
aggregate functions.
An aggregate function computes a single result from multiple input rows.
For example, there are aggregates to compute the
count
, sum
,
avg
(average), max
(maximum) and
min
(minimum) over a set of rows.
As an example, we can find the highest and lowest salaries with
SELECT MAX(sal) AS Highest_Salary, MIN(sal) AS Lowest_Salary
FROM emp
highest_salary | lowest_salary
----------------+---------------
5000.00 | 800.00
(1 row)
If we wanted to know who the highest paid employee in the organization
was, we might try
SELECT ename FROM emp WHERE sal = MAX(sal); WRONG
but this will not work since the aggregate
max
cannot be used in the
WHERE clause. (This restriction exists because
the WHERE clause determines the rows that will
go into the aggregation stage; so it has to be evaluated before
aggregate functions are computed.)
However, as is often the case
the query can be restated to accomplish the intended result, here
by using a subquery:
SELECT ename FROM emp
WHERE sal = (SELECT MAX(sal) FROM emp);
ename
-------
KING
(1 row)
This is OK because the subquery is an independent computation
that computes its own aggregate separately from what is happening
in the outer query.
Aggregates are also very useful in combination with GROUP
BY clauses. For example, we can get the highest
salary for each department with
SELECT deptno, MAX(sal)
FROM emp
GROUP BY deptno;
deptno | max
--------+---------
30 | 2850.00
20 | 3000.00
10 | 5000.00
(3 rows)
which gives us one output row per department. Each aggregate
result is computed over the table rows matching that
department. We can filter these grouped rows
using HAVING:
SELECT deptno, MAX(sal)
FROM emp
GROUP BY deptno
HAVING AVG(sal) > 2000;
deptno | max
--------+---------
30 | 2850.00
20 | 3000.00
10 | 5000.00
(3 rows)
which gives us the same results for only those departments
that have an average salary greater than 2000. Finally,
if we only care about the highest paid employees for each
department whose names begin with "K",
we might do
SELECT deptno, MAX(sal)
FROM emp
WHERE ename LIKE 'K%'
GROUP BY deptno
HAVING AVG(sal) > 2000;
It is important to understand the interaction between aggregates and
SQL's WHERE and HAVING clauses.
The fundamental difference between WHERE and
HAVING is this: WHERE selects
input rows before groups and aggregates are computed (thus, it controls
which rows go into the aggregate computation), whereas
HAVING selects group rows after groups and
aggregates are computed. Thus, the
WHERE clause must not contain aggregate functions;
it makes no sense to try to use an aggregate to determine which rows
will be inputs to the aggregates. On the other hand,
HAVING clause always contains aggregate functions.
(Strictly speaking, you are allowed to write a HAVING
clause that doesn't use aggregates, but it's wasteful: The same condition
could be used more efficiently at the WHERE stage.)
Observe that we can apply the employee name restriction in WHERE
clause since it needs no aggregate.
This is more efficient than adding the restriction to
HAVING, because we avoid doing the grouping
and aggregate calculations for all rows that fail the WHERE
check.