SQL Aggregate Functions Explained: COUNT, SUM, AVG, MIN, MAX

Every real application — whether it's a shopping site showing 'Average rating: 4.7 stars', a bank dashboard showing 'Total spent this month: $1,240', or an HR tool reporting 'Headcount: 342 employees' — is powered by SQL aggregate functions running behind the scenes. These are not advanced features. They're the bread and butter of database work, and you'll use them in almost every non-trivial query you ever write. Understanding them unlocks the ability to answer real business questions from raw data.

The problem they solve is simple but critical: raw data is overwhelming. A sales table might have five million rows. Nobody wants to read five million rows. Stakeholders want answers: How many orders came in today? What's the average order value? Who placed the biggest single order? Aggregate functions collapse all that noise into a single, meaningful number. They let your database do the heavy lifting so your application code stays clean.

By the end of this article, you'll know exactly what each of the five core aggregate functions does, when to reach for each one, how to combine them with GROUP BY to slice data into meaningful segments, and the exact mistakes that trip up beginners. You'll also walk away with answers to the interview questions that actually come up in SQL screening rounds.

What Aggregate Functions Actually Do (And Why They Exist)

A regular SQL query like SELECT name FROM employees returns one row per employee — every single row that matches your WHERE clause. An aggregate function changes that contract entirely. Instead of 'give me each row', you're saying 'give me one summary value calculated across many rows'.

Think of it like this: a regular query is a photocopier — it reproduces individual records. An aggregate function is a calculator — it takes all those records and produces a single result.

There are five aggregate functions you need to know cold:

• COUNT — how many rows exist?
• SUM — what do all the values add up to?
• AVG — what's the average value?
• MIN — what's the smallest value?
• MAX — what's the largest value?

Every one of them follows the same pattern: you call the function and pass in a column name (or * for COUNT). The database reads every relevant row in that column and returns one number.

Before writing any queries, let's set up the table we'll use throughout this whole article so everything feels connected and consistent.

-- Create a realistic orders table for a small online store
CREATE TABLE orders (
    order_id     INT PRIMARY KEY,
    customer     VARCHAR(50),
    product      VARCHAR(50),
    category     VARCHAR(30),
    quantity     INT,
    unit_price   DECIMAL(8, 2),
    total_amount DECIMAL(8, 2),
    order_date   DATE
);

-- Populate with realistic sample data
INSERT INTO orders VALUES
  (1,  'Alice',   'Laptop',     'Electronics', 1, 999.99, 999.99,  '2024-01-05'),
  (2,  'Bob',     'Headphones', 'Electronics', 2,  49.99,  99.98,  '2024-01-07'),
  (3,  'Alice',   'Desk Chair', 'Furniture',   1, 249.00, 249.00,  '2024-01-10'),
  (4,  'Carol',   'Keyboard',   'Electronics', 3,  79.99, 239.97,  '2024-02-01'),
  (5,  'Bob',     'Monitor',    'Electronics', 1, 399.99, 399.99,  '2024-02-14'),
  (6,  'David',   'Bookshelf',  'Furniture',   1, 149.00, 149.00,  '2024-02-20'),
  (7,  'Carol',   'Webcam',     'Electronics', 2,  89.99, 179.98,  '2024-03-03'),
  (8,  'Alice',   'Lamp',       'Furniture',   3,  34.99, 104.97,  '2024-03-15'),
  (9,  'Eve',     'Laptop',     'Electronics', 1, 999.99, 999.99,  '2024-03-22'),
  (10, 'David',   'Mouse',      'Electronics', 4,  29.99, 119.96,  '2024-04-01');

-- Verify the data loaded correctly
SELECT * FROM orders;

COUNT, SUM and AVG — Answering the 'How Many?' and 'How Much?' Questions

These three functions answer the most common business questions you'll ever face.

COUNT tells you how many rows exist. It comes in two flavours that behave very differently: COUNT(*) counts every row including those with NULL values, while COUNT(column_name) only counts rows where that specific column is NOT NULL. This distinction trips people up constantly — we'll cover it in the mistakes section.

SUM adds up all the values in a numeric column. There's no secret here — it works exactly like your phone calculator. Pass it a column of numbers and it returns their total. NULL values are silently ignored (treated as zero for the purpose of the sum).

AVG calculates the arithmetic mean. Under the hood, it's doing SUM divided by COUNT of non-null values. That last part matters — if some rows have NULL in the column, AVG skips them entirely, which can make the average higher or lower than you'd expect.

Let's put all three to work on our orders table with real, meaningful business questions.

-- QUESTION 1: How many orders have we received in total?
SELECT COUNT(*) AS total_orders
FROM orders;
-- COUNT(*) counts every single row regardless of NULL values
-- Result: 10

-- QUESTION 2: How many DISTINCT customers have placed an order?
SELECT COUNT(DISTINCT customer) AS unique_customers
FROM orders;
-- DISTINCT tells COUNT to ignore duplicates — Alice appears 3 times but counts as 1
-- Result: 5

-- QUESTION 3: What is the total revenue across all orders?
SELECT SUM(total_amount) AS total_revenue
FROM orders;
-- Adds up every value in the total_amount column
-- Result: 3542.83

-- QUESTION 4: What is the average order value?
SELECT AVG(total_amount) AS average_order_value
FROM orders;
-- Divides the sum of total_amount by the count of non-null total_amount rows
-- Result: 354.283

-- QUESTION 5: Combine all three in a single dashboard-style query
SELECT
    COUNT(*)                        AS total_orders,
    COUNT(DISTINCT customer)        AS unique_customers,
    SUM(total_amount)               AS total_revenue,
    ROUND(AVG(total_amount), 2)     AS avg_order_value
FROM orders;
-- ROUND(value, 2) trims AVG to 2 decimal places — much cleaner for money

MIN and MAX — Finding the Extremes in Your Data

MIN and MAX are the simplest aggregate functions to understand, and also the most underestimated. They don't just work on numbers — they work on dates, times, and text too. MIN on a date column gives you the earliest date. MAX on a text column gives you the last name alphabetically. That versatility makes them genuinely powerful.

The classic use case is finding the cheapest and most expensive items, the first and last order dates, or the customer who spent the most in a single transaction. Notice that MIN and MAX give you the extreme VALUE — they don't automatically tell you which row that value came from. If you need the full row (e.g., which customer placed the biggest order), you'll use a subquery or ORDER BY with LIMIT — but that's a topic for another day.

For now, let's see all five aggregate functions side by side on our orders table. Seeing them together in one query is the fastest way to understand what each one contributes.

-- MIN and MAX on numeric columns
SELECT
    MIN(total_amount) AS cheapest_order,
    MAX(total_amount) AS most_expensive_order
FROM orders;
-- Scans every value in total_amount and picks the smallest and largest

-- MIN and MAX on DATE columns — works just like numbers
SELECT
    MIN(order_date) AS first_order_date,
    MAX(order_date) AS most_recent_order_date
FROM orders;
-- MIN returns the earliest (oldest) date, MAX returns the latest (newest)

-- The complete store summary — all five aggregates in one shot
SELECT
    COUNT(*)                        AS total_orders,
    SUM(total_amount)               AS total_revenue,
    ROUND(AVG(total_amount), 2)     AS avg_order_value,
    MIN(total_amount)               AS smallest_order,
    MAX(total_amount)               AS largest_order,
    MIN(order_date)                 AS trading_since,
    MAX(order_date)                 AS last_order_on
FROM orders;
-- This single query gives a full business health snapshot
-- A real dashboard would run something very close to this

GROUP BY — Where Aggregate Functions Become Truly Powerful

Everything so far has given us a single row summarising the whole table. That's useful, but GROUP BY is where aggregate functions level up. Instead of one grand total, GROUP BY lets you calculate a separate aggregate for each group — for example, total revenue broken down by category, or number of orders per customer.

Here's the mental model: GROUP BY splits your table into invisible piles. If you GROUP BY category, the database makes one pile for 'Electronics' rows and one for 'Furniture' rows. Then the aggregate function runs separately on each pile and returns one result row per pile.

The golden rule of GROUP BY is this: every column in your SELECT that is NOT inside an aggregate function MUST appear in your GROUP BY clause. If you select customer and SUM(total_amount), you must GROUP BY customer. Forgetting this is the single most common SQL error beginners make, and every database will throw an error (or give you nonsensical results) if you violate it.

Let's see GROUP BY transform our dataset into genuinely actionable business intelligence.

-- EXAMPLE 1: Total revenue broken down by product category
SELECT
    category,
    COUNT(*)                    AS number_of_orders,
    SUM(total_amount)           AS category_revenue,
    ROUND(AVG(total_amount), 2) AS avg_order_value
FROM orders
GROUP BY category;
-- The database splits all 10 rows into 2 piles (Electronics, Furniture)
-- then runs each aggregate independently on each pile

-- EXAMPLE 2: Spending per customer — who are our best customers?
SELECT
    customer,
    COUNT(*)                    AS orders_placed,
    SUM(total_amount)           AS total_spent,
    MAX(total_amount)           AS biggest_single_order
FROM orders
GROUP BY customer
ORDER BY total_spent DESC; -- DESC = largest first, so top customers appear at top

-- EXAMPLE 3: HAVING — filtering AFTER the group is formed
-- (WHERE filters rows BEFORE grouping; HAVING filters groups AFTER)
SELECT
    customer,
    SUM(total_amount) AS total_spent
FROM orders
GROUP BY customer
HAVING SUM(total_amount) > 500  -- only show customers who spent more than $500
ORDER BY total_spent DESC;
-- You cannot use WHERE here — WHERE runs before groups exist
-- HAVING is the WHERE clause for aggregated results

Frequently Asked Questions