Skip to content

Aggregation Functions

Summarize data with COUNT, SUM, AVG, MIN, and MAX aggregate functions.

What you'll learn

  • What aggregate functions are and when to use them
  • COUNT, SUM, AVG, MIN, MAX — the five core aggregates
  • The difference between COUNT(*) and COUNT(column)
  • Combining aggregates with WHERE to filter before aggregating
  • Why NULL values affect aggregate results

Concept

Aggregate Functions

An aggregate function takes many rows and returns a single value. They are the foundation of reporting and analytics — "how many orders?", "what is the average price?", "what is the total revenue?". The five core aggregates are:

| Function | Returns | Example | |----------|---------|---------| | COUNT(*) | Number of rows | COUNT(*) FROM orders | | SUM(col) | Total of numeric values | SUM(total) FROM orders | | AVG(col) | Average value | AVG(price) FROM products | | MIN(col) | Smallest value | MIN(price) FROM products | | MAX(col) | Largest value | MAX(created_at) FROM orders |

COUNT(*) vs COUNT(column)

COUNT(*) counts all rows, including those with NULL values. COUNT(column) counts only non-NULL values in that column. COUNT(DISTINCT column) counts unique non-NULL values. This distinction matters when your column has NULLs — COUNT(email) will skip rows where email is NULL.

Filtering with WHERE

You can combine aggregates with WHERE to filter rows before aggregating. SELECT AVG(price) FROM products WHERE category = 'electronics' gives the average price of electronics only. The WHERE clause is applied first, then the aggregate is computed over the remaining rows.

NULL Handling

All aggregate functions except COUNT(*) ignore NULL values. AVG(price) sums non-NULL prices and divides by the count of non-NULL prices — NULL rows do not pull the average toward zero. This is usually what you want, but be aware of it when your data has many NULLs.

Example

              -- Core aggregates over the orders table
SELECT
  COUNT(*)              AS total_orders,
  SUM(total)            AS revenue,
  AVG(total)            AS avg_order_value,
  MIN(total)            AS smallest_order,
  MAX(total)            AS largest_order
FROM orders
WHERE status = 'completed';

-- Count distinct categories and total products
SELECT
  COUNT(DISTINCT category) AS unique_categories,
  COUNT(*)                 AS total_products
FROM products;

-- Revenue per category (preview of GROUP BY)
SELECT category, SUM(total) AS revenue
FROM orders
GROUP BY category;
            

The first query computes five aggregates in one pass. The second shows COUNT(DISTINCT). The third previews GROUP BY — coming next.

Try it

  • SQL Formatter

    Format queries with multiple aggregate functions.

  • CSV to JSON

    Export aggregated query results as JSON for charts.

Common mistakes

The mistake

Using COUNT(column) when you mean COUNT(*)

The fix

COUNT(column) skips NULLs. If you want the total number of rows regardless of NULLs, use COUNT(*).

The mistake

Selecting non-aggregated columns alongside aggregates without GROUP BY

The fix

You cannot mix plain columns with aggregates unless you GROUP BY those columns. The database will error or return unpredictable results.

Practice Aggregation Functions

18 exercises

Practice Aggregation Functions

Look up unfamiliar terms

A beginner-friendly glossary explains jargon in plain English — variables, functions, DOM, Promise, and more.

View glossary

Build real projects

Apply what you learned by building guided projects with starter code and solutions.

View projects

Decode error messages

Plain-English explanations of common errors — what they mean, why they happen, and how to fix them.

View errors