Stop 03 · SQL for Data AnalysisTopics · 2/10

Aggregate Functions (COUNT, SUM, AVG)

In short

Almost every business number, like total revenue, number of orders or average basket size, is one aggregate function away. Learn these five and you can answer half of all requests.

Functions that collapse many rows into a single summary number — COUNT rows, SUM a numeric column, AVG for the mean, MIN/MAX for range — the building blocks of almost every business metric.

The jaadu stacks coins from a piggy bank into towers of different heights.

Think of it like this

Breaking the piggy bank

You smash your gullak and spread the coins out. How many coins? (COUNT). How much money? (SUM). Average coin value? (AVG). The smallest and biggest coin? (MIN, MAX). Many coins in, one answer out. That's an aggregate.

Watch it happen

Five order amounts, one number each

250

400

NULL

150

700

1/6Five orders. One has no amount recorded, so it's NULL.

The big ideas

What you need to know

COUNT(*) vs COUNT(col)

COUNT(*) counts all rows. COUNT(column) counts only non-NULL values in that column.

COUNT(DISTINCT ...)

COUNT(DISTINCT customer_id) gives unique customers, not total orders. A very common interview question.

NULLs are skipped

SUM, AVG, MIN and MAX ignore NULLs. Use COALESCE(amount, 0) if missing should count as zero.

Many rows become one

Without GROUP BY, an aggregate returns a single row for the whole filtered table.

See it in code

Walk through the code

sql

1SELECT2  COUNT(*)                    AS total_orders,3  COUNT(DISTINCT customer_id) AS unique_customers,4  SUM(amount)                 AS revenue,5  ROUND(AVG(amount), 2)       AS avg_order_value,6  MAX(amount)                 AS biggest_order7FROM orders8WHERE order_date >= '2026-01-01';

1/3Total orders vs unique customers. The gap tells you how many people order more than once.

A tiny KPI summary for the year so far, in one row.

Save yourself the pain

Common mistakes

Oops

Using COUNT(*) when you meant 'how many customers'.

Do this instead

Use COUNT(DISTINCT customer_id). One customer with ten orders is still one customer.

Oops

Assuming AVG treats missing values as zero.

Do this instead

AVG ignores NULLs. Decide on purpose whether missing means zero, and use COALESCE if it does.

Your mission

Build a KPI row

~20 min

Quick check

A table has 10 orders from 4 customers. What does COUNT(DISTINCT customer_id) return?

Go deeper

1Write queries calculating total, average, and count for a sample sales table.
2Learn the difference between COUNT(*) and COUNT(column_name).
3Practice combining an aggregate with a WHERE filter.