Aggregate Functions

Aggregate functions let you compute a single result from a set of rows. Instead of seeing every individual row, you can answer questions like "How many employees do we have?" or "What is the average salary?" These functions are foundational to data analysis in SQL.

The Big Five Aggregate Functions

PostgreSQL provides several aggregate functions, but five are used constantly:

COUNT: Counting Rows

COUNT is the most commonly used aggregate function. It comes in two forms:

COUNT(*) -- Count All Rows

SELECT COUNT(*) FROM employees;

This counts every row in the employees table, including rows with NULL values in any column.

COUNT(column_name) -- Count Non-NULL Values

SELECT COUNT(manager_id) FROM employees;

This counts only rows where manager_id is not NULL. If some employees have no manager (NULL), those rows are skipped.

COUNT(DISTINCT column_name) -- Count Unique Values

SELECT COUNT(DISTINCT department) FROM employees;

This counts how many different departments exist, ignoring duplicates.

SUM: Adding Values Together

SUM adds up all the values in a numeric column:

SELECT SUM(salary) FROM employees;

This returns the total payroll -- the sum of every employee's salary.

You can also combine SUM with a WHERE clause to sum a subset:

SELECT SUM(salary) FROM employees
WHERE department = 'Engineering';

This gives you the total salary expenditure for just the Engineering department.

Important: SUM ignores NULL values. If a salary is NULL, it is simply skipped in the calculation.

AVG: Calculating the Mean

AVG returns the arithmetic mean (average) of a numeric column:

SELECT AVG(salary) FROM employees;

Like SUM, the AVG function ignores NULL values. This is an important detail: if you have 10 employees but 2 have NULL salaries, AVG divides by 8, not 10.

You can round the result for cleaner output:

SELECT ROUND(AVG(salary), 2) AS average_salary FROM employees;

MIN and MAX: Finding Extremes

MIN returns the smallest value, and MAX returns the largest:

SELECT MIN(salary) AS lowest_salary,
       MAX(salary) AS highest_salary
FROM employees;

These functions work on more than just numbers. They also work with text (alphabetical order) and dates:

SELECT MIN(hire_date) AS earliest_hire,
       MAX(hire_date) AS latest_hire
FROM employees;

SELECT MIN(name) AS first_alphabetically,
       MAX(name) AS last_alphabetically
FROM employees;

Combining Multiple Aggregates

You can use several aggregate functions in the same SELECT statement to build a summary:

SELECT
    COUNT(*) AS total_employees,
    SUM(salary) AS total_payroll,
    ROUND(AVG(salary), 2) AS average_salary,
    MIN(salary) AS lowest_salary,
    MAX(salary) AS highest_salary
FROM employees;

This single query gives you a complete salary overview of your workforce.

Aggregates with WHERE

You can filter rows before aggregation using WHERE. The aggregate function only sees rows that pass the filter:

SELECT
    COUNT(*) AS active_employees,
    AVG(salary) AS avg_active_salary
FROM employees
WHERE is_active = true;

Here is another example using the orders table:

SELECT
    COUNT(*) AS total_orders,
    SUM(total_price) AS revenue,
    AVG(total_price) AS avg_order_value
FROM orders
WHERE status = 'completed';

Aggregates with DISTINCT

You can use DISTINCT inside aggregate functions to operate on unique values only:

SELECT
    COUNT(customer_name) AS total_orders,
    COUNT(DISTINCT customer_name) AS unique_customers
FROM orders;

This tells you how many orders exist versus how many distinct customers placed orders.

A Note About NULL

All aggregate functions (except COUNT(*)) ignore NULL values. This behavior is worth remembering:

• SUM of {10, NULL, 20} is 30, not NULL
• AVG of {10, NULL, 20} is 15 (divides by 2, not 3)
• COUNT(column) of {10, NULL, 20} is 2
• COUNT(*) of the same three rows is 3

If you want NULLs to be treated as zero, use COALESCE:

SELECT AVG(COALESCE(salary, 0)) FROM employees;

Try It Yourself

1. Count the total number of orders in the orders table.

SELECT COUNT(*) AS total_orders FROM orders;

2. Find the most expensive and least expensive order by total_price.

SELECT
    MIN(total_price) AS cheapest_order,
    MAX(total_price) AS most_expensive_order
FROM orders;

3. Calculate the total revenue from all orders, the average order value, and the number of unique customers.

SELECT
    SUM(total_price) AS total_revenue,
    ROUND(AVG(total_price), 2) AS avg_order_value,
    COUNT(DISTINCT customer_name) AS unique_customers
FROM orders;

4. Find the average salary of employees hired after January 1, 2020.

SELECT ROUND(AVG(salary), 2) AS avg_recent_salary
FROM employees
WHERE hire_date > '2020-01-01';