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

Summarise rows into a single value using aggregate functions.

Overview

Aggregate functions collapse multiple rows into a single summary value. The five most common are COUNT (how many rows), SUM (total value), AVG (mean), MIN (smallest), and MAX (largest). They are almost always used with GROUP BY to compute summaries per group for example, total sales per month or average price per category. Key rules: aggregate functions ignore NULLs (except COUNT(*)). COUNT(*) counts all rows including NULLs; COUNT(col) counts only non-NULL values. AVG(col) is SUM(col) / COUNT(col) it skips NULLs rather than treating them as zero. Aggregate expressions cannot be used in WHERE (the filter happens before aggregation). Use HAVING to filter after aggregation.

Key Points

•COUNT(*) count all rows including NULLs
•COUNT(col) count non-NULL values in col
•SUM(col) total of all non-NULL values
•AVG(col) mean of non-NULL values (= SUM/COUNT)
•MIN(col) / MAX(col) smallest/largest non-NULL value
•All aggregates ignore NULLs except COUNT(*)

Examples

Basic aggregate functions

SELECT
  COUNT(*)           AS total_orders,
  COUNT(shipped_at)  AS shipped_orders,
  SUM(total)         AS revenue,
  AVG(total)         AS avg_order_value,
  MIN(total)         AS smallest_order,
  MAX(total)         AS largest_order
FROM orders;

Aggregates per group

SELECT
  category,
  COUNT(*)           AS product_count,
  AVG(price)         AS avg_price,
  MIN(price)         AS cheapest,
  MAX(price)         AS most_expensive
FROM products
GROUP BY category
ORDER BY avg_price DESC;

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

Overview

Key Points

Examples

Exercises

Knowledge Check

What is the difference between COUNT(*) and COUNT(col)?