COUNT, SUM, AVG, MIN, MAX — aggregate functions explained

Aggregates Collapse Many Rows Into One

Aggregate functions take a set of rows and return a single value. Without GROUP BY, they operate on every row in the table (or the rows surviving WHERE).

SELECT
  COUNT(*)            AS total_orders,
  COUNT(shipped_at)   AS shipped_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
WHERE created_at >= '2024-01-01';

COUNT(*) vs COUNT(column)

COUNT(*) counts all rows, including those with NULLs. COUNT(column) counts only rows where that column is NOT NULL. This difference matters: if 200 orders exist but only 150 have a shipped_at date, COUNT(*) returns 200, COUNT(shipped_at) returns 150.

NULLs Are Silently Ignored

SUM, AVG, MIN, and MAX all skip NULL values automatically. If a discount column has NULLs mixed with numbers, AVG averages only the non-NULL rows — it does not treat NULL as zero. This is often not what you want:

-- Treat NULL discount as 0 for averaging
SELECT AVG(COALESCE(discount, 0)) AS avg_discount FROM orders;

DISTINCT Inside Aggregates

COUNT(DISTINCT column) counts unique non-NULL values:

-- How many unique customers placed orders?
SELECT COUNT(DISTINCT customer_id) FROM orders;