Counting and summarizing: COUNT, AVG, MAX

So far every SELECT returned one row for each row of the table. Aggregate functions do the opposite: they read many rows and return a single value that summarises them.

The most common ones are:

• COUNT(*) — how many rows there are.
• AVG(column) — the average of numeric values (ignores NULL).
• MAX(column) / MIN(column) — the maximum / minimum value.
• SUM(column) — the sum of numeric values.

SELECT COUNT(*),
       AVG(salary),
       MAX(salary)
FROM employees;

When the aggregate is applied to the whole table (without GROUP BY, which you will see in the next module) the result is a single row, regardless of how many rows the source table has.

Combining with WHERE

WHERE filters before aggregation: the aggregate only works on the rows that passed the filter.

-- Stipendio medio dei soli dipendenti del Marketing (department_id = 3):
SELECT AVG(salary)
FROM employees
WHERE department_id = 3;

Try it

Review exercise