BeginnerLesson 6 of 305 min read

Counting and Summarising with Aggregate Functions

Summarise many rows into one answer with COUNT, SUM, AVG, MIN and MAX, and see how NULL values change the numbers.

In this lesson you will

  • Use COUNT, SUM, AVG, MIN and MAX
  • Tell COUNT(*) from COUNT(column)
  • Know how aggregates treat NULL
  • Round numbers for display

About 22 minutes

One answer from many rows

Maya’s next questions are not about single rows. How many products does she sell? How many units are in stock? What is the average price? Each question squeezes many rows into one number. SQL has aggregate functions for exactly that, and they appear everywhere in reports.

The five you will use most

  • COUNT: how many rows, or how many non-NULL values in a column.
  • SUM: the total of a numeric column.
  • AVG: the average, which is the sum divided by the number of values.
  • MIN and MAX: the smallest and largest value.

With no grouping, an aggregate query returns exactly one row. All the rows that pass the WHERE filter are combined into it.

Four aggregate queries

-- 1
SELECT COUNT(*) AS products, COUNT(stock) AS with_stock_known, SUM(stock) AS units FROM products

-- 2
SELECT ROUND(AVG(price), 2) AS avg_price, MIN(price) AS cheapest, MAX(price) AS dearest FROM products

-- 3
SELECT ROUND(AVG(stock), 2) AS avg_stock FROM products

-- 4
SELECT COUNT(*) AS shipped_orders FROM orders WHERE status = 'shipped'

-- 5
SELECT COUNT(DISTINCT category) AS categories FROM products

-- 6
SELECT COUNT(*) AS needs_attention FROM products WHERE stock IS NULL OR stock < 10
-- 1
products | with_stock_known | units
8 | 7 | 123
-- 2
avg_price | cheapest | dearest
9.81 | 4.00 | 18.00
-- 3
avg_stock
17.57
-- 4
shipped_orders
7
-- 5
categories
3
-- 6
needs_attention
3

Query 1 counted the products in two ways. COUNT(*) gave 8, the number of rows. COUNT(stock) gave 7, because it counts only rows where stock is not NULL, and the mist spray has no stock figure. The total of the stock column is 123 units, which, again, skips the NULL. Query 2 shows an average price of 9.81, with the cheapest product at 4.00 and the dearest at 18.00. Query 3 gives an average stock of 17.57, and here the NULL matters. The average divides by the 7 known values, not by all 8 products. If the missing stock were counted as zero, the average would be about 15.4, which is a different answer to a different question. Query 4 counted the 7 shipped orders, using WHERE to filter before counting. Query 5 counted the different categories with COUNT(DISTINCT category), and found 3. Query 6 counted the products that need attention, meaning stock below 10 or unknown, and found 3: the orchid, the glass pot and the mist spray.

Counting with conditions and distinct values

Two small variations are used all the time. Putting DISTINCT inside COUNT counts how many different values a column holds, as in query 5, which is a quick way to answer ‘how many kinds?’. And a WHERE clause before the count lets you count only the rows that interest you, as in queries 4 and 6. Query 6 also shows NULL handled on purpose: the condition says stock is NULL or stock is below 10, so the product with unknown stock is counted deliberately, in line with the advice of lesson 4. A count with a thoughtful WHERE is the quickest way to turn a vague worry, such as ‘are we running low?’, into a number you can act on.

Aggregates and NULL

Aggregate functions skip NULLs, with the exception of COUNT(*), which counts rows. That is usually what you want, but it can mislead. An average over a column that is mostly NULL describes only the few rows that have a value. Before you quote an average or a total in a report, count how many values it is based on. A short query comparing COUNT(*) with COUNT(column) takes seconds and prevents embarrassing claims.

Rounding and exactness

Averages often have long decimal tails, so wrap them in ROUND to the precision you need. Remember that rounding is for display. Keep the exact number if you will use it in further calculations. Also be careful with money in floating-point columns, which can show tiny errors. For real accounts, use exact decimal types or whole pence, as lesson 1 mentioned.

Habits for aggregates

  • Always check COUNT(*) against COUNT(column) for NULLs.
  • Filter with WHERE before aggregating, to answer a narrower question.
  • Round for display only.
  • State what the average is over, for example ‘average price of products in the shop’.
Common mistake: trusting an average built on missing data

Quoting an average without knowing how many values it covers. If most rows are NULL, the average describes only a few.

Made-up sample data

The figures come from a tiny made-up database. Aggregate functions are standard, but how a database shows decimals and treats rounding can differ, so check your documentation.

Write queries for the number of customers with a known city and the total number of customers. Compare the two numbers and explain the difference.

Practice for this lesson

Check what you learned

Five quick questions. Pick an answer and you will see straight away whether it is right and why.

What does COUNT(*) count?

  1. Only non-NULL values in one column
  2. Only distinct values
  3. Columns
  4. All rows

COUNT star counts rows, whatever the values are.

Why did COUNT(stock) give 7, not 8?

  1. It skips NULL values and one product has no stock figure
  2. It counts only plants
  3. One product was deleted
  4. It counts distinct values

The mist spray has a NULL stock.

What does an aggregate query without grouping return?

  1. One row per product
  2. No rows
  3. One row
  4. An error

All filtered rows are combined into one.

How does AVG treat NULL values?

  1. It counts them as zero
  2. It fails
  3. It counts them as one
  4. It skips them, so it divides by the number of known values

The average stock used seven values.

Why compare COUNT(*) with COUNT(column)?

  1. To speed up the query
  2. To see how many values an average or total rests on
  3. To sort the rows
  4. To remove NULLs

It reveals missing values.

Summary

  • Aggregates squeeze many rows into one answer.
  • COUNT(*) counts rows; COUNT(column) skips NULLs.
  • SUM, AVG, MIN and MAX ignore NULLs.
  • Check how many values a number rests on.

Next lesson

Lesson 7 groups rows, so each category or customer gets its own summary.

Questions

Do aggregates work on text?

COUNT does. MIN and MAX can compare text alphabetically in most databases. SUM and AVG need numbers.

What is the difference between COUNT(*) and COUNT(1)?

In most databases they give the same result. Both count rows.

Is the shop data real?

No. It is a tiny made-up sample.

Progress is saved in this browser.