Courses/SQL/Aggregate Functions

Lesson 7 • Intermediate Track

Aggregate Functions

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.

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)

Before you start: finish JOIN Operations so you're comfortable selecting rows from one or more tables. The in-browser editor lets you write and edit SQL; to run it, copy your query into a free playground like sqliteonline.com or db-fiddle.com. Every example below shows the expected result so you can check yourself.

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.

Result:

id	product_name	category	price	stock
1	Wireless Mouse	Electronics	24.99	120
2	Coffee Mug	Kitchen	9.5	300
3	Mechanical Keyboard	Electronics	79	45
4	Notebook	Stationery	3.25	500
5	Desk Lamp	Home	32	80
6	USB-C Cable	Electronics	12.99	200

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.

📊 Real-world analogy

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.

Function	Returns	NULLs
COUNT(*)	Number of rows	Counts every row
COUNT(col)	Non-NULL values in a column	Skips NULLs
SUM(col)	Total of all values	Skips NULLs
AVG(col)	Average (mean) value	Skips NULLs
MIN(col)	Smallest value	Skips NULLs
MAX(col)	Largest value	Skips NULLs

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.

COUNT(*) — count every row

Six rows collapse into one number.

Try it Yourself »

SQL

-- 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.

Result — 1 row:

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) — count non-NULL values

All match here because nothing is NULL.

Try it Yourself »

SQL

-- 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(*).

Result — 1 row:

all_rows	priced_rows	categorised
6	6	6

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

COUNT(DISTINCT ...) — unique values

Electronics appears 3 times but counts once.

Try it Yourself »

SQL

-- 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).

Result — 1 row:

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: COUNT(*)

Replace the ___ with the aggregate that counts rows.

Try it Yourself »

SQL

-- 🎯 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 — add up a column

Add the six stock levels into one total.

Try it Yourself »

SQL

-- 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.

Result — 1 row:

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 — the mean value

Sum of prices divided by the row count.

Try it Yourself »

SQL

-- 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.

Result — 1 row:

average_price
26.955

Your Turn: total the stock

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

🎯 Your Turn: SUM(stock)

Total the stock column across all products.

Try it Yourself »

SQL

-- 🎯 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.

Pro tip: MIN/MAX give you the extreme value, not the row it came from. To find which product is cheapest, you'll use ORDER BY price LIMIT 1 instead — a later lesson.

MIN & MAX — extremes in one query

Cheapest and most expensive prices together.

Try it Yourself »

SQL

-- 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).

Result — 1 row:

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.

Aggregate + WHERE

Count only the Electronics products.

Try it Yourself »

SQL

-- 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.

Result — 1 row:

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.

ROUND(AVG(...), 2)

Average Electronics price, rounded to 2 dp.

Try it Yourself »

SQL

-- 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

Result — 1 row:

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.

📘 Quick Reference

Syntax	Purpose
COUNT(*)	Number of rows (counts NULLs)
COUNT(col)	Non-NULL values in a column
COUNT(DISTINCT col)	Number of unique values
SUM(col)	Total of all values
AVG(col)	Average (mean) value
MIN(col)	Smallest value
MAX(col)	Largest value
ROUND(AVG(col), 2)	Average, to 2 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

Rounded average price of the Electronics category.

Try it Yourself »

SQL

-- 🎯 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

Aggregate Functions

What You'll Learn

Our Sample Table: products

1. What Is an Aggregate Function?

2. COUNT — How Many Rows?

COUNT(*) — count every row

COUNT(column) — count non-NULL values

COUNT(DISTINCT ...) — unique values

Your Turn: count the products

🎯 Your Turn: COUNT(*)

3. SUM & AVG — Totals and Averages

SUM — add up a column

AVG — the mean value

Your Turn: total the stock

🎯 Your Turn: SUM(stock)

4. MIN & MAX — Finding Extremes

MIN & MAX — extremes in one query

5. Filtering First with WHERE, Tidying with ROUND

Aggregate + WHERE

ROUND(AVG(...), 2)

Common Errors (and the fix)

📘 Quick Reference

Frequently Asked Questions

Mini-Challenge: Average Electronics Price

🎯 Mini-Challenge

🎉 Lesson Complete

Cookie & Privacy Settings

Our Sample Table: `products`