PostgreSQL - MAX Function

PostgreSQL Aggregate Functions: MAX, MIN, AVG, SUM, COUNT

Aggregate functions perform a calculation on a set of rows and return a single summary value. They are essential for data analysis and reporting. While MAX() is a great starting point, it’s best learned alongside its companions: MIN, AVG, SUM, and COUNT.

Setup: Sample ‘products’ Table

Let’s work with a products table to explore these functions:

CREATE TABLE products (
    id SERIAL PRIMARY KEY,
    name VARCHAR(100),
    category VARCHAR(50),
    price NUMERIC(10, 2),
    stock_quantity INT
);

INSERT INTO products (name, category, price, stock_quantity) VALUES
('Laptop', 'Electronics', 1200.00, 10),
('Mouse', 'Electronics', 25.00, 150),
('Desk Chair', 'Furniture', 150.00, 30),
('Keyboard', 'Electronics', 75.00, 75),
('Bookshelf', 'Furniture', 250.00, 15);

Core Aggregate Functions

These functions can be used on an entire table to get a single result.

-- Find the most and least expensive product prices
SELECT MAX(price) AS max_price, MIN(price) AS min_price FROM products;
-- Result: max_price = 1200.00, min_price = 25.00

-- Find the average price and total value of all stock
SELECT AVG(price) AS average_price, SUM(price * stock_quantity) AS total_stock_value FROM products;
-- Result: average_price = 340.00, total_stock_value = 26025.00

Understanding COUNT

COUNT is special because its behavior depends on what you give it:

• COUNT(*): Counts the total number of rows in the set. SELECT COUNT(*) FROM products; -- Returns 5
• COUNT(column_name): Counts the number of rows where column_name is NOT NULL.
• COUNT(DISTINCT column_name): Counts the number of unique, non-null values in a column. SELECT COUNT(DISTINCT category) FROM products; -- Returns 2

Grouping Data with GROUP BY

The true power of aggregate functions is unlocked when combined with the GROUP BY clause. GROUP BY collapses multiple rows into a single summary row based on column values. It allows you to run aggregate calculations for each group.

Example: Get a Summary for Each Category

Let’s find the number of products, the average price, and the most expensive product within each category.

SELECT
    category,
    COUNT(*) AS number_of_products,
    ROUND(AVG(price), 2) AS average_price,
    MAX(price) AS max_price
FROM products
GROUP BY category;

This query produces a powerful summary report:

  category   | number_of_products | average_price | max_price
-------------+--------------------+---------------+-----------
 Electronics |                  3 |        433.33 |   1200.00
 Furniture   |                  2 |        200.00 |    250.00
(2 rows)

Filtering Groups with HAVING

What if you want to filter the results based on the aggregated value? You can’t use WHERE, because WHERE filters rows before aggregation happens. For this, you need the HAVING clause.

Example: Find Categories with More Than 2 Products

SELECT
    category,
    COUNT(*) AS number_of_products
FROM products
GROUP BY category
HAVING COUNT(*) > 2;

This query first groups by category and counts the products, then filters those groups, keeping only the ones that satisfy the HAVING condition.

  category   | number_of_products
-------------+--------------------
 Electronics |                  3
(1 row)

Best Practices and Key Concepts

• WHERE vs. HAVING: Remember the order of operations: WHERE filters rows before GROUP BY, and HAVING filters groups after GROUP BY.
• NULL Handling: Most aggregate functions (except COUNT(*)) ignore NULL values in their calculations.
• Performance: Creating an index on the column(s) used in your GROUP BY clause can significantly speed up aggregation queries on large tables.
• Modern Aggregates: PostgreSQL has many other powerful aggregate functions like STRING_AGG (to concatenate strings into one) and ARRAY_AGG (to gather values into an array), which are great for advanced data shaping.