SQL Aggregate Functions: SUM, COUNT, AVG, MIN, MAX (2026)

Quick Answer: What Do Aggregate Functions Do?

The Survey That Lied by Omission

A customer satisfaction survey has a satisfaction_score column, and 40% of customers simply didn't answer that question — those rows sit as NULL, not zero. Someone runs SELECT AVG(satisfaction_score) FROM responses; and drops the result straight into a slide titled "Average Customer Satisfaction." The number is real and the math is correct — but it's the average among the 60% who responded, silently, with no indication anywhere in the output that 40% of the base was excluded from the calculation entirely.

This isn't a bug in AVG — it's documented, standard behavior across every SQL database. The problem is that it's easy to forget, and the output gives no visible signal that anything was excluded. A report built this way isn't wrong exactly, but it's answering a narrower question than the title implies, and the reader has no way to know that from the number alone.

SUM: Adding Values, With a NULL Catch

SUM adds up every non-null value in the specified column across the group.

-- total revenue across all orders
SELECT SUM(order_total) AS total_revenue
FROM orders;

-- SUM returns NULL, not 0, when there are zero matching rows
SELECT SUM(order_total) AS total_revenue
FROM orders
WHERE status = 'a_status_that_never_occurs';

COUNT and COUNT DISTINCT

COUNT has three real variations, and mixing them up is a common source of subtly wrong metrics.

-- every row, regardless of NULLs anywhere
SELECT COUNT(*) AS total_orders FROM orders;

-- only rows where discount_code is not NULL
SELECT COUNT(discount_code) AS orders_with_discount FROM orders;

-- unique customers, not unique orders
SELECT COUNT(DISTINCT customer_id) AS unique_customers FROM orders;

COUNT(DISTINCT customer_id) on an orders table answers "how many different customers ordered," which is a completely different number from COUNT(*) ("how many orders total") the moment any customer has placed more than one order. Reaching for the wrong one of the three is an easy way to report "customer count" numbers that are actually order counts, or vice versa.

AVG and the NULL Trap

AVG computes a mean, but its denominator is the count of non-null rows — not the total row count. This is exactly the mechanism behind this article's opening scenario.

-- if 40% of rows have a NULL satisfaction_score, this averages only the other 60%
SELECT AVG(satisfaction_score) AS avg_satisfaction
FROM responses;

-- more honest: show the average alongside how many rows it's based on
SELECT
  AVG(satisfaction_score) AS avg_satisfaction,
  COUNT(satisfaction_score) AS respondents,
  COUNT(*) AS total_customers
FROM responses;

The second version doesn't fix AVG's behavior — nothing needs fixing, it's working exactly as specified — but it makes the exclusion visible to whoever reads the report, by showing the response rate alongside the average itself. That single addition is often the difference between a number that's technically correct and a number that's actually honest about what it represents.

MIN and MAX Beyond Numbers

MIN and MAX aren't limited to numeric columns — they work on any type with a defined ordering, which includes text and dates.

-- a customer's first and most recent order
SELECT
  MIN(order_date) AS first_order,
  MAX(order_date) AS most_recent_order
FROM orders
WHERE customer_id = 1024;

-- alphabetically first and last product name
SELECT MIN(product_name), MAX(product_name)
FROM products;

On dates, MIN and MAX are the standard way to answer "when did this first happen" and "when did this most recently happen" — a customer's signup-to-first-purchase timeline, the most recent login, the earliest recorded transaction. On text, they respect whatever collation and sort order the database uses, the same rules covered in the ORDER BY article earlier in this cluster.

Aggregates Without GROUP BY

An aggregate function doesn't require GROUP BY — without it, the entire filtered result set is treated as a single implicit group.

-- one row back: the whole table collapsed into a single summary
SELECT
  COUNT(*) AS total_orders,
  SUM(order_total) AS total_revenue,
  AVG(order_total) AS avg_order_value
FROM orders
WHERE order_date >= '2026-01-01';

This is the pattern behind almost every single-number dashboard tile — "total orders this month," "total revenue," "average order value" — one aggregated row summarizing everything that survived the WHERE clause, with no GROUP BY needed because there's only one group: everything.

Conditional Aggregation: CASE WHEN Inside an Aggregate

Combining an aggregate with CASE WHEN computes a value only for rows matching a condition — all within one pass over the data, without a separate query per condition.

-- count shipped and cancelled orders side by side, in one query
SELECT
  SUM(CASE WHEN status = 'shipped' THEN 1 ELSE 0 END) AS shipped_count,
  SUM(CASE WHEN status = 'cancelled' THEN 1 ELSE 0 END) AS cancelled_count,
  SUM(CASE WHEN status = 'shipped' THEN order_total ELSE 0 END) AS shipped_revenue
FROM orders;

This is one of the most useful patterns for building dashboard-style reports: instead of running one query per status or writing a separate query for every slice you need, a single conditional aggregation query returns multiple related metrics side by side in one result row, computed from a single scan of the table.

How Each Function Treats NULL: A Quick Reference

The pattern worth memorizing: COUNT(*) is the one true exception that includes NULLs, since it's counting rows rather than evaluating a specific column's values. Every other function on this list quietly skips NULL and can return NULL itself when nothing is left to aggregate.

Mistakes That Give Away a Beginner

• Presenting AVG as representing the full row count — the exact trap from this article's opening. Report the denominator (COUNT of the aggregated column) alongside any average built on a column with NULLs.
• Assuming SUM returns 0 for an empty result and not handling a NULL result explicitly with COALESCE where a guaranteed number matters.
• Using COUNT(*) when COUNT(DISTINCT column) was needed, conflating a row count with a unique-value count.
• Running a separate query per status or category instead of using conditional aggregation to compute several related metrics in one pass.
• Forgetting that MIN/MAX on text sorts alphabetically, which can produce surprising results on mixed-case or numeric-looking text columns depending on collation.

Practice: Write These Three Queries Yourself

Using the same orders table (order_id, customer_id, price, quantity, order_date, status), try writing these:

Frequently Asked Questions

Conclusion: Know What Got Excluded

SUM, COUNT, AVG, MIN, and MAX are the building blocks behind almost every summary metric a report will ever show — and the one habit worth carrying forward from this article is checking what each function silently excluded before trusting the number. AVG and SUM skip NULLs; SUM and AVG return NULL, not zero, over an empty set; COUNT(*) is the lone exception that counts everything. None of that makes the functions wrong — it makes them precise in a way that's easy to misread if you don't know the rule.

Practice the three queries above against a real table, then move to the next article in this cluster, which covers subqueries and CTEs — how to structure multi-step logic that a single aggregate query can't express on its own.