SQL Aggregations — COUNT, SUM, AVG, MIN, MAX Explained

What You Will Learn

• The five core SQL aggregation functions
• How each one handles NULL values
• Using DISTINCT inside aggregations
• How aggregations combine with WHERE

Why This Topic Matters

Every report you will ever build is built on these five functions. Total revenue = SUM. Order count = COUNT. Average delivery time = AVG. Cheapest product = MIN. Most expensive = MAX. Master them now and the rest of SQL becomes much easier — especially GROUP BY in lesson 21, which is just aggregations applied per group.

The Five Functions

Sample Data

We extend our orders table with one more column, delivery_min, and one NULL row:

COUNT — How Many Rows?

SELECT COUNT(*) FROM orders;            → 5  (counts all rows)
SELECT COUNT(amount) FROM orders;       → 5  (counts non-NULL amount values)
SELECT COUNT(delivery_min) FROM orders; → 4  (skips the NULL!)

COUNT(DISTINCT ...) — Unique Values

SELECT COUNT(DISTINCT customer) FROM orders;  → 4  (Anita, Ravi, Mira, Sam)
SELECT COUNT(DISTINCT city) FROM orders;      → 3  (Mumbai, Chennai, Kolkata)

Useful for "how many unique customers placed an order?"

SUM — Total of a Numeric Column

SELECT SUM(amount) FROM orders;       → 4850
SELECT SUM(delivery_min) FROM orders; → 159  (ignores the NULL row)

SUM automatically ignores NULLs — they do not contribute 0, they are skipped. If you SUM a column where every value is NULL, the result is NULL (not 0).

AVG — Average of Non-NULL Values

SELECT AVG(delivery_min) FROM orders;  → 39.75  (159 / 4, not 159 / 5)

This is a critical point: AVG ignores NULLs entirely. It does not treat them as 0. The denominator is the count of non-NULL values, not the count of all rows.

If you wanted "average delivery time including orders that have no recorded time as 0", you would write:

SELECT AVG(COALESCE(delivery_min, 0)) FROM orders;  → 31.8  (159 / 5)

COALESCE(delivery_min, 0) replaces NULL with 0 before averaging. Whether that is the right thing to do depends on the business question — usually it is not, because missing data is not the same as zero.

MIN and MAX — Smallest and Largest

SELECT MIN(amount) FROM orders;  → 500
SELECT MAX(amount) FROM orders;  → 1500
SELECT MIN(delivery_min) FROM orders;  → 28  (ignores NULL)
SELECT MAX(delivery_min) FROM orders;  → 52

MIN and MAX work on dates too:

SELECT MIN(ordered_at) AS first_order,
       MAX(ordered_at) AS latest_order
FROM orders;

Multiple Aggregations in One Query

You can compute several at once:

SELECT
  COUNT(*) AS order_count,
  SUM(amount) AS total_revenue,
  AVG(amount) AS avg_order_value,
  MIN(amount) AS smallest_order,
  MAX(amount) AS largest_order
FROM orders;

Result:

One query, five numbers. This is the foundation of every "summary" report.

Combining Aggregations with WHERE

WHERE filters before the aggregation runs. So this:

SELECT SUM(amount) AS mumbai_revenue
FROM orders
WHERE city = 'Mumbai';

First filters to Mumbai rows, then sums them. Result: 500 + 1500 = 2000.

The Aggregate Without GROUP BY Trap

When you use an aggregate function without GROUP BY, the entire result is one row — even if the query also selects a non-aggregated column. This:

SELECT customer, SUM(amount)
FROM orders;

Returns one row in strict SQL (most databases reject this; SQLite returns the first customer with the total sum — misleading). The fix is GROUP BY, which we cover in lesson 21.

NULL Handling Summary

Common Mistakes

1. Using COUNT(column) when you mean COUNT(*). If the column has NULLs, you undercount rows.
2. Forgetting that AVG ignores NULLs. If 30% of delivery times are NULL, your "average" only reflects the 70% that were recorded — which may be a biased sample.
3. Mixing aggregated and non-aggregated columns. SELECT customer, SUM(amount) FROM orders is invalid in most databases. Use GROUP BY.
4. Summing a column stored as text. SUM silently returns 0 or an error. Convert types first.
5. Confusing COUNT(DISTINCT col) with DISTINCT COUNT(*). The latter is not valid syntax. Use COUNT(DISTINCT col) to count unique values.

Practical Exercise (10 minutes)

Using the sample data above, write queries to compute:

1. Total amount across all orders.
2. Number of orders (use COUNT(*)).
3. Average delivery time (ignoring NULLs).
4. Smallest and largest amount.
5. Number of unique customers.
6. Total amount for Mumbai orders only.
7. Average amount for orders placed by Anita.

Mini Challenge

Write a query that returns: total orders, total revenue, average revenue per order (computed two ways — once with AVG and once with SUM/COUNT), and the difference between them. The two averages should match if every row has an amount — but if NULLs exist, they will differ. Understanding why is the whole point of this lesson.

Key Takeaways