Aggregate Functions

Reviewed & published by Brayan K

By the end of this lesson you'll be able to collapse a whole table into a single answer — a count, a total, an average, a smallest or a largest value — and combine those with WHERE and ROUND to produce clean summary reports. Aggregates are how raw rows become the numbers people actually read.

Part of the free SQL course at LearnCodingFast — hands-on lessons with examples you run in your browser, plus practice exercises and a quick quiz.

What You'll Learn

• Count rows two ways: COUNT(*) vs COUNT(column)
• Why COUNT(column) skips NULLs
• Add and average with SUM and AVG
• Find extremes with MIN and MAX
• Filter the rows first using WHERE
• Tidy averages with ROUND(AVG(...), 2)

Our Sample Table: products

Every query in this lesson runs against this little products table — the same one from the SELECT lesson. Six rows is small enough to add up by hand, so you can verify every answer yourself.

1. What Is an Aggregate Function?

An aggregate function takes many rows and boils them down to a single value. Instead of six rows of products, you get one number — how many there are, what they cost in total, the average price, the cheapest, the dearest.

That collapsing is the whole point: SELECT price FROM products gives you six prices, but SELECT AVG(price) FROM products gives you exactly one. Five aggregates cover almost everything you'll do: COUNT, SUM, AVG, MIN, MAX.

An aggregate is a calculator pointed at a column. You have a shelf of products — COUNT(*) tells you how many items are on it, SUM(stock) adds up every unit, AVG(price) gives the typical price, and MIN/MAX point at the cheapest and most expensive.

2. COUNT — How Many Rows?

COUNT(*) counts rows, plain and simple. It never looks at the values inside — it just tallies how many rows came back. Run it on our table and six rows collapse into the single number 6.

-- The table this lesson uses. Every later block queries it, and the
-- "Try it Yourself" button carries this setup along so each snippet runs.
CREATE TABLE products (
    id           INTEGER PRIMARY KEY,
    product_name TEXT,
    category     TEXT,
    price        REAL,
    stock        INTEGER
);

INSERT INTO products (id, product_name, category, price, stock) VALUES
    (1, 'Wireless Mouse',      'Electronics', 24.99, 120),
    (2, 'Coffee Mug',          'Kitchen',      9.50, 300),
    (3, 'Mechanical Keyboard', 'Electronics', 79.00,  45),
    (4, 'Notebook',            'Stationery',   3.25, 500),
    (5, 'Desk Lamp',           'Home',        32.00,  80),
    (6, 'USB-C Cable',         'Electronics', 12.99, 200);

-- COUNT(*) counts EVERY row, full stop — it doesn't look at values
SELECT COUNT(*) AS total_products
FROM products;

-- One row collapses out of six: the answer is a single number, 6.
-- This is the key idea of aggregation — many rows in, one value out.

-- ✅ Expected result:
-- total_products
-- 6

COUNT(column) is different: it counts only the rows where that column is not NULL. NULL means "no value recorded" — an empty cell. In our table no column is empty, so every COUNT(column) also equals 6.

-- COUNT(column) counts only the rows where that column is NOT NULL
SELECT
    COUNT(*)        AS all_rows,      -- 6  (every row)
    COUNT(price)    AS priced_rows,   -- 6  (every product has a price)
    COUNT(category) AS categorised    -- 6  (every product has a category)
FROM products;

-- Here all three match because no column is NULL.
-- The moment a column has gaps, COUNT(column) drops below COUNT(*).

-- ✅ Expected result:
-- all_rows | priced_rows | categorised
-- 6 | 6 | 6

Add DISTINCT and COUNT tallies how many different values appear, ignoring repeats.

-- COUNT(DISTINCT column) counts how many DIFFERENT values exist
SELECT COUNT(DISTINCT category) AS num_categories
FROM products;

-- Our six products span Electronics, Kitchen, Stationery, Home,
-- so the result is 4 (Electronics appears three times but counts once).

-- ✅ Expected result:
-- num_categories
-- 4

Your Turn: count the products

Fill in the blank to count every product in the table. The expected result is in the comments so you can check yourself.

-- 🎯 YOUR TURN — fill in the blank, then press "Try it Yourself"
-- Goal: count how many products are in the table.

SELECT ___ AS total_products   -- 👉 the aggregate that counts every row
FROM products;

-- ✅ Expected result: a single value, total_products = 6

3. SUM & AVG — Totals and Averages

SUM(column) adds every value in a numeric column. Point it at stock and it adds the six stock levels into one grand total.

-- SUM adds up all the values in a numeric column
SELECT SUM(stock) AS total_units
FROM products;

-- 120 + 300 + 45 + 500 + 80 + 200 = 1245 units in stock across the shop.

-- ✅ Expected result:
-- total_units
-- 1245

AVG(column) is the mean — the sum of the column divided by how many values it added. Notice the result isn't a tidy number; you'll fix that with ROUND in section 5.

-- AVG returns the mean: SUM of the column divided by how many values
SELECT AVG(price) AS average_price
FROM products;

-- (24.99 + 9.50 + 79.00 + 3.25 + 32.00 + 12.99) / 6 = 26.955
-- Raw averages are often ugly — see the ROUND section below.

-- ✅ Expected result:
-- average_price
-- 26.955

Your Turn: total the stock

Two blanks this time — name the adding function and the column it totals.

-- 🎯 YOUR TURN — fill in BOTH blanks.
-- Goal: find the total number of units in stock across all products.

SELECT ___(___) AS total_units   -- 👉 the adding function, and the stock column
FROM products;

-- ✅ Expected result: total_units = 1245
--    (120 + 300 + 45 + 500 + 80 + 200)

4. MIN & MAX — Finding Extremes

MIN returns the smallest value in a column and MAX the largest. You can ask for both in a single query, and they work on more than numbers — on text they go alphabetically (A first), on dates earliest to latest.

-- MIN finds the smallest value, MAX the largest — in ONE query
SELECT
    MIN(price) AS cheapest,        -- 3.25  (the Notebook)
    MAX(price) AS most_expensive   -- 79.00 (the Mechanical Keyboard)
FROM products;

-- MIN/MAX also work on text (A–Z order) and dates (earliest/latest).

-- ✅ Expected result:
-- cheapest | most_expensive
-- 3.25 | 79

5. Filtering First with WHERE, Tidying with ROUND

Aggregates respect WHERE. The filter runs first, throwing away rows that don't match, and the aggregate then summarises only what's left. So COUNT(*) ... WHERE category = 'Electronics' counts just the Electronics rows.

-- Aggregates obey WHERE: only the matching rows are summarised
SELECT COUNT(*) AS electronics_count
FROM products
WHERE category = 'Electronics';

-- Three products are Electronics (Mouse, Keyboard, Cable), so the answer is 3.
-- WHERE runs FIRST, then the aggregate runs on whatever survived.

-- ✅ Expected result:
-- electronics_count
-- 3

AVG often produces a long, ugly decimal. Wrap it in ROUND(value, 2) to cut it to two decimal places — perfect for prices and reports.

-- Wrap AVG in ROUND to keep results readable
SELECT ROUND(AVG(price), 2) AS avg_price
FROM products
WHERE category = 'Electronics';

-- Electronics prices: 24.99, 79.00, 12.99
-- Mean = 116.98 / 3 = 38.9933...  ->  ROUND(..., 2) = 38.99

-- ✅ Expected result:
-- avg_price
-- 38.99

Common Errors (and the fix)

• COUNT(column) came back lower than expected: that's by design — it skips NULLs. If 6 rows exist but one has a NULL price, COUNT(price) is 5 while COUNT(*) is 6. Use COUNT(*) when you mean "all rows".
• Mixing a plain column with an aggregate: SELECT product_name, AVG(price) FROM products; errors (or returns nonsense) — a single average can't line up with six product names. Aggregate or add a GROUP BY (next lesson). Error reads like "column products.product_name must appear in the GROUP BY clause".
• AVG on an integer column rounds down: in some databases AVG of integers does integer division — the average of 3 and 4 can come back as 3, not 3.5. Force decimals with AVG(price * 1.0) if your column is an integer type.
• AVG looks too high: remember it divides by the count of non-NULL values, not the row count. If only 4 of 6 rows have a value, it divides by 4. Use AVG(COALESCE(col, 0)) to treat blanks as zero.
• Wrong decimals: ROUND(AVG(price)) with no second argument rounds to a whole number. Write ROUND(AVG(price), 2) for two decimal places.

Frequently Asked Questions

Q: What's the real difference between COUNT(*) and COUNT(column)?

COUNT(*) counts every row no matter what. COUNT(column) counts only rows where that column has a value — it skips NULLs. They're equal only when the column has no blanks.

Q: Why does my query error when I select a column alongside an aggregate?

An aggregate returns one value, but a plain column returns one value per row — they don't fit in the same result. Select only aggregates, or group the rows with GROUP BY (the next lesson).

Q: Do aggregates count NULL values?

Only COUNT(*) does. SUM, AVG, MIN, MAX and COUNT(column) all silently ignore NULLs. That's usually what you want, but it's why an average can look surprisingly high.

Q: Can I use an aggregate inside WHERE?

No — WHERE filters individual rows before any aggregating happens, so WHERE COUNT(*) > 5 is invalid. To filter on an aggregate you use HAVING, which you'll meet in the GROUP BY lesson.

Mini-Challenge: Average Electronics Price

Put it all together — a brief, a blank canvas, and the expected result in the comments. Write it, then copy it into a playground to confirm.

-- 🎯 MINI-CHALLENGE
-- Using ONLY what this lesson covered (AVG, WHERE, ROUND):
--   1. Filter to the Electronics category with WHERE
--   2. Take the AVG of price
--   3. Round it to 2 decimal places and alias it avg_electronics_price
--
-- ✅ Expected result: a single value, avg_electronics_price = 38.99
--    (prices 24.99, 79.00, 12.99 -> 116.98 / 3 = 38.9933... -> 38.99)

-- your query here

🎉 Lesson Complete

• ✅ An aggregate collapses many rows into a single value
• ✅ COUNT(*) counts all rows; COUNT(column) skips NULLs
• ✅ SUM totals a column; AVG gives the mean
• ✅ MIN and MAX return the extremes in one query
• ✅ WHERE filters first; ROUND(AVG(...), 2) tidies the result
• ✅ Next: GROUP BY & HAVING — run these aggregates per category instead of over the whole table

Practice quiz

Continue this course

• Previous: JOIN Operations
• Next: GROUP BY & HAVING — Group results and filter groups with GROUP BY and HAVING
• Quick reference: SQL cheat sheet › Aggregation (more)