PostgreSQL Aggregate Functions

What is an aggregate function?

An aggregate function takes a set of rows as input and returns a single row as output. Instead of showing you every individual value in a column, PostgreSQL reduces them down to one number that describes the whole group, or each group if you use GROUP BY.

Aggregate functions are the backbone of reporting queries. Whenever you need a total, an average, a headcount, or a highest or lowest value, you reach for an aggregate function instead of pulling every row back to your application and doing the math yourself.

The core aggregate functions

PostgreSQL ships with a full set of built-in aggregates. The five you will use constantly are:

• COUNT() - counts the number of rows
• SUM() - adds up numeric values
• AVG() - computes the arithmetic mean
• MIN() - finds the smallest value
• MAX() - finds the largest value

SELECT COUNT(*) AS total_employees
FROM employees;

Aggregating with GROUP BY

Aggregate functions become far more powerful when paired with GROUP BY. Instead of one summary value for the entire table, GROUP BY splits the rows into buckets and computes the aggregate separately for each bucket.

SELECT department_id, AVG(salary) AS avg_salary
FROM employees
GROUP BY department_id
ORDER BY avg_salary DESC;

SELECT department_id, COUNT(*) AS headcount
FROM employees
GROUP BY department_id
HAVING COUNT(*) > 5;