Lesson 7 of 20

In progress

Course progress

SQL

COUNT, SUM, AVG and friends

Turn many rows into one number with the five aggregates, and learn exactly which rows each one counts.

Aggregates collapse a set of rows into one value. They are the reason a database can answer "how much" without sending every row to your application.

What you will learn

  • Use COUNT, SUM, AVG, MIN and MAX.
  • Predict how each handles NULL.
  • Combine aggregates in one query.

The five

SQL
Editable. Runs in your browser.





Press Run to see the output.

Five columns, one row: the aggregates each read the whole table and produce a value.

MIN and MAX also work on text, using the collation order, and on dates stored as ISO text. MIN(name) gives the alphabetically first name, which is occasionally exactly what a report needs.

NULL handling

Every aggregate except COUNT(*) ignores NULLs:

SQL
Editable. Runs in your browser.





Press Run to see the output.

Two rows have a discount, so COUNT(discount_paise) is 2, and the average divides by 2 rather than 5. SUM of the two values is 1500.

That behaviour is usually right, and it is worth knowing when it is not. An average order value that skips orders with a null total will be too high. Decide which question you are answering, then choose AVG(column) or AVG(COALESCE(column, 0)).

SUM and empty sets

SQL
Editable. Runs in your browser.





Press Run to see the output.

No rows match, so the result is a single row containing NULL, not 0 and not zero rows. That distinction breaks naive code:

SQL
Editable. Runs in your browser.





Press Run to see the output.

Wrap sums in COALESCE when a report needs a number. The same applies to AVG and MIN/MAX.

Expressions inside aggregates

SQL
Editable. Runs in your browser.





Press Run to see the output.

Aggregates take expressions, so you can compute revenue before summing. Keep the arithmetic inside the sum: summing after rounding, or rounding before summing, gives different answers, and a financial report should say which one it uses.

Notice the alias reuse problem: gross_paise cannot be referenced in the same SELECT list in most databases. Repeat the expression, or compute it in a subquery.

Rounding and division

SQL
Editable. Runs in your browser.





Press Run to see the output.

ROUND takes the value and the number of decimals. Integer division appears again: SUM(...) / 100 truncates, so divide by 100.0.

Aggregates ignore row order

SUM does not care about order; AVG neither. Only MIN/MAX depend on the ordering of values, and they use the type’s natural order. If you need "the value from the most recent row", that is not an aggregate: it is ORDER BY ... LIMIT 1 or a window function.

One row, many answers

Multiple aggregates in one SELECT are evaluated over the same filtered set. That makes a dashboard query cheap:

SQL
Editable. Runs in your browser.





Press Run to see the output.

Every column describes the same rows, which is what makes them comparable. Adding a second query with a different WHERE means the numbers no longer describe the same population, and comparing them becomes misleading.

Aggregates and indexes

An aggregate that reads every row cannot use an index to skip work, but an index can still help: a covering index holds the aggregated column, so the engine reads the index instead of the full table. A query filtered by region benefits from an index on region, because the filter runs first and the aggregate then sees only the matching rows.

MIN and MAX are the exception that surprises people. With an index on the column, the engine can read the first or last entry and stop, turning a full scan into a single lookup. That is why a "latest timestamp" query is fast when the timestamp is indexed and slow when it is not. None of this changes the result, only the time it takes.

Forgetting that COUNT(*) on a filtered query with no matches returns 0, while SUM returns NULL. A report that mixes the two shows "0 orders, no revenue" and blanks where the two should agree.

For the west region, return the number of rows, the total units, the gross revenue in rupees rounded to two decimals, and the largest single line value.

Show solution
SQL
Editable. Runs in your browser.





Press Run to see the output.

Three west rows, 35 units, and the largest line is the pen order at 50,000 paise. MAX over an expression works like any other aggregate, and dividing by 100.0 keeps the decimals.

Recap

  • COUNT(*) counts rows; the other aggregates skip NULLs.
  • SUM over no rows is NULL; wrap it in COALESCE for reports.
  • Compute inside the aggregate, and round only the final value.

Next, GROUP BY applies these aggregates per category instead of to the whole table.

Can I use an aggregate in WHERE?
No. `WHERE` runs before aggregation. Use `HAVING` for conditions on aggregates.
What does MAX return for text?
The last value in collation order, which is useful for “latest” on ISO dates and rarely useful for names.

Progress is saved in this browser only. No account, no email.

Admin

About the author

Admin

Instructor and technical writer at tutorials.in

Writes beginner lessons for tutorials.in. Each lesson is checked against the language or tool documentation, and examples are run before publishing.

Reviewed by the editorial team before publishing. Last updated November 16, 2026.