Aggregate

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.

Learn Python, JavaScript, Java and more with free interactive lessons, real projects and AI-powered help. Beginner-friendly.

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.

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.

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.

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

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

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

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

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.

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

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.

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.

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.

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

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

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.

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.

Practice quiz

• Browse all free courses
• Cheat sheets & quizzes
• Coding dictionary