SQL Aggregate Functions: COUNT, SUM, AVG, MIN, MAX

Your head of finance wants three numbers before Monday: total revenue collected, average order value, and the size of the largest single order. All three live in the orders table. Without aggregate functions, you'd be exporting rows to a spreadsheet and doing it manually. With them, you write one query and you're done.

What Aggregate Functions Do and How to Use Them

Aggregate functions collapse multiple rows into a single value. Instead of returning one row per record, they return one row per group, or one row for the whole table if you don't group at all.

The five core functions are:

Here's all five in one query against the orders table:

SELECT
  COUNT(*) AS total_orders,
  SUM(total_amount) AS revenue,
  AVG(total_amount) AS avg_order_value,
  MIN(total_amount) AS smallest_order,
  MAX(total_amount) AS largest_order
FROM orders o

Each function runs across every row in the table and returns one number. The aliases after AS are optional, but they make the output readable. Without them, the column headers are just the function calls themselves.

COUNT in Detail: Rows vs Non-NULL Values

COUNT behaves differently depending on what you pass it, and the difference matters.

COUNT(*) counts every row in the result set, regardless of NULL values. Use this when you want to know how many records exist.

SELECT COUNT(*) AS total_customers
FROM customers c

COUNT(column) counts only the rows where that column is not NULL. If some customers have no phone number recorded, COUNT(phone) gives you a lower number than COUNT(*).

SELECT
  COUNT(*) AS total_customers,
  COUNT(c.phone) AS customers_with_phone
FROM customers c

This is useful for spotting gaps in your data. If those two numbers differ significantly, you know some records are incomplete.

Filtering Before You Aggregate

Aggregate functions run after [WHERE](sqlround.com/lessons/sql-where-clause) filters the rows. This means you can narrow the dataset first and then summarise what's left.

SELECT
  COUNT(*) AS delivered_orders,
  SUM(o.total_amount) AS delivered_revenue
FROM orders o
WHERE o.status = 'delivered'

Only delivered orders are counted and summed here. The WHERE clause runs first, the aggregate functions run second.

If you want to filter based on the result of an aggregate, you need [HAVING](sqlround.com/lessons/sql-having-clause) instead of WHERE. HAVING filters after aggregation. WHERE filters before.

Combining Aggregates with GROUP BY

Running aggregate functions across the whole table gives you one row. Running them with [GROUP BY](sqlround.com/lessons/sql-group-by) gives you one row per group. This is where aggregate functions become genuinely useful for reporting.

SELECT
  o.status,
  COUNT(*) AS order_count,
  SUM(o.total_amount) AS total_revenue
FROM orders o
GROUP BY o.status

This returns one row per distinct status value, with a count and a revenue total for each. The status column becomes the grouping dimension. Everything else collapses into aggregates.

You can group by more than one column. Add employee_id to the GROUP BY and you get revenue broken down by both status and employee.

SELECT
  o.status,
  o.employee_id,
  COUNT(*) AS order_count
FROM orders o
GROUP BY o.status, o.employee_id

Every column in SELECT that isn't an aggregate must appear in GROUP BY. That rule doesn't have exceptions.

Common Mistakes

1. Selecting a non-aggregated column without GROUP BY

Including a plain column alongside an aggregate function without GROUP BY causes an error or unpredictable results depending on the database.

-- wrong
SELECT o.status, COUNT(*)
FROM orders o

Either aggregate the column or add it to GROUP BY.

-- correct
SELECT o.status, COUNT(*) AS order_count
FROM orders o
GROUP BY o.status

2. Using WHERE to filter on an aggregate result

WHERE runs before aggregation, so it has no access to the results of aggregate functions.

-- wrong
SELECT o.status, COUNT(*) AS order_count
FROM orders o
WHERE COUNT(*) > 10
GROUP BY o.status

Use HAVING to filter on aggregate results after grouping.

-- correct
SELECT o.status, COUNT(*) AS order_count
FROM orders o
GROUP BY o.status
HAVING COUNT(*) > 10

3. Using AVG on a column that contains NULLs without accounting for them

AVG ignores NULL values automatically, which means the denominator is the count of non-NULL rows, not the total row count. This can produce a higher average than you'd expect if many rows are NULL.

-- wrong assumption: this averages only non-NULL salaries, not all employees
SELECT AVG(e.salary) AS avg_salary
FROM employees e

If you need NULL rows treated as zero, convert them first with COALESCE.

-- correct: treats NULL salary as 0 in the average
SELECT AVG(COALESCE(e.salary, 0)) AS avg_salary
FROM employees e

Quick Recap

• COUNT, SUM, AVG, MIN, and MAX each collapse multiple rows into a single value.
• COUNT(*) counts all rows; COUNT(column) skips NULLs in that column.
• Aggregate functions without GROUP BY return one row for the whole table.
• With GROUP BY, you get one row per distinct group, and every non-aggregated column in SELECT must appear in GROUP BY.
• WHERE filters rows before aggregation; HAVING filters after.
• AVG ignores NULL values, which can affect your results if the column has gaps.

SELECT COUNT(*) AS total_payments
FROM payments pay

SELECT
  SUM(pay.amount) AS total_collected,
  AVG(pay.amount) AS avg_payment,
  MAX(pay.amount) AS largest_payment
FROM payments pay

SELECT pay.method, SUM(pay.amount) AS total_by_method
FROM payments pay
GROUP BY pay.method

SELECT pay.method, SUM(pay.amount) AS total_by_method
FROM payments pay
GROUP BY pay.method
HAVING SUM(pay.amount) > 1000
ORDER BY total_by_method DESC

SELECT
  e.department,
  COUNT(*) AS employee_count,
  AVG(e.salary) AS avg_salary
FROM employees e
GROUP BY e.department
ORDER BY avg_salary DESC