PostgreSQL Aggregate Functions

Aggregate functions perform a calculation across a set of rows and return a single result. Instead of returning one value per row, they collapse multiple rows into one summary value. They are essential for answering questions like "How many customers are there?", "What is the average salary?", or "What is the total revenue?"

Common Aggregate Functions

COUNT()

Count All Rows

SELECT COUNT(*) FROM employees;

COUNT(*) counts every row, including rows that have NULL values in some columns.

Count Non-NULL Values in a Column

SELECT COUNT(phone) FROM employees;

This counts only rows where the phone column is not NULL. Rows with a NULL phone number are excluded from the count.

Count Distinct Values

SELECT COUNT(DISTINCT department) FROM employees;

This returns the number of unique departments, not the total number of employees.

SUM()

SELECT SUM(salary) FROM employees;

Returns the total of all salary values combined. SUM ignores NULL values automatically.

SELECT SUM(price * quantity) AS total_revenue FROM order_items;

An expression can be used inside SUM. This calculates total revenue by multiplying price and quantity for each row, then adding all the results.

AVG()

SELECT AVG(salary) FROM employees;

Returns the average salary across all employees. NULL values are ignored in the calculation.

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

ROUND is applied here to limit the average to two decimal places, making the output cleaner.

MIN() and MAX()

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

Both functions can be used in the same query. They also work on text columns, where MIN returns the alphabetically first value and MAX returns the last.

SELECT MIN(name) AS first_name_alphabetically,
       MAX(name) AS last_name_alphabetically
FROM students;

Using Aggregate Functions with WHERE

A WHERE clause can be added to filter rows before the aggregate function is applied:

SELECT AVG(salary) FROM employees WHERE department = 'IT';

The average is calculated only for rows in the IT department. All other rows are excluded before the aggregation happens.

SELECT COUNT(*) FROM orders WHERE status = 'completed';

This counts only completed orders.

Aggregates on Expressions

Aggregate functions can operate on computed expressions, not just column names:

SELECT
    SUM(price * quantity) AS total_value,
    AVG(price * quantity) AS average_order_value
FROM order_items;

The COALESCE Function with Aggregates

When a column contains NULL values, aggregates like AVG and SUM skip them. But sometimes NULLs should be treated as zero. COALESCE replaces NULL with a fallback value:

SELECT SUM(COALESCE(bonus, 0)) AS total_bonuses FROM employees;

COALESCE(bonus, 0) returns the bonus value if it is not NULL, or 0 if it is NULL. This ensures NULL bonuses are counted as zero rather than being ignored.

Using Multiple Aggregates Together

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

All five aggregate functions are used in one query to produce a comprehensive salary summary in a single result row.

Example: Analyzing an Orders Table

CREATE TABLE orders (
    id SERIAL PRIMARY KEY,
    customer_name VARCHAR(100),
    product VARCHAR(100),
    quantity INT,
    unit_price NUMERIC(8, 2),
    status VARCHAR(20)
);

INSERT INTO orders (customer_name, product, quantity, unit_price, status)
VALUES
    ('Alice', 'Laptop', 1, 1200.00, 'completed'),
    ('Bob', 'Phone', 2, 800.00, 'completed'),
    ('Carol', 'Tablet', 1, 600.00, 'pending'),
    ('Alice', 'Keyboard', 3, 50.00, 'completed'),
    ('Dan', 'Monitor', 1, 400.00, 'cancelled'),
    ('Eva', 'Laptop', 2, 1200.00, 'completed');

-- Total number of orders
SELECT COUNT(*) AS total_orders FROM orders;

-- Total revenue from completed orders only
SELECT SUM(quantity * unit_price) AS revenue
FROM orders
WHERE status = 'completed';

-- Average order value
SELECT ROUND(AVG(quantity * unit_price), 2) AS avg_order_value
FROM orders;

-- Cheapest and most expensive products
SELECT MIN(unit_price) AS cheapest, MAX(unit_price) AS most_expensive
FROM orders;

-- How many distinct customers placed orders?
SELECT COUNT(DISTINCT customer_name) AS unique_customers FROM orders;

Key Points

• Aggregate functions reduce multiple rows into a single result value.
• COUNT(*) counts all rows; COUNT(column) counts non-NULL values only.
• SUM and AVG ignore NULL values automatically.
• A WHERE clause filters rows before the aggregate function is applied.
• Multiple aggregate functions can be used in the same SELECT query.
• COALESCE can convert NULL values to a default before passing them to an aggregate.
• Aggregate functions become even more powerful when combined with GROUP BY, which is the next topic.