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

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

Aggregate functions compute a single result from a set of input values. They are the building blocks of data summarization and reporting in SQL.

The Five Core Aggregates

SELECT
  COUNT(*) AS total_orders,
  COUNT(DISTINCT customer_id) AS unique_customers,
  SUM(total_amount) AS revenue,
  AVG(total_amount) AS avg_order_value,
  MIN(order_date) AS first_order,
  MAX(order_date) AS last_order
FROM orders
WHERE order_date >= '2025-01-01';

COUNT Variations

COUNT(*)           -- Counts all rows (including NULLs)
COUNT(column)      -- Counts non-NULL values in column
COUNT(DISTINCT col) -- Counts unique non-NULL values

NULL Handling

All aggregate functions except COUNT(*) ignore NULL values:

-- Given scores: [100, NULL, 80, NULL, 90]
SELECT
  COUNT(*) AS total_rows,      -- 5
  COUNT(score) AS non_null,    -- 3
  AVG(score) AS average,       -- 90 (270/3, NULLs excluded)
  SUM(score) AS total          -- 270
FROM test_scores;

To include NULLs in calculations, use COALESCE:

SELECT AVG(COALESCE(score, 0)) FROM test_scores;  -- 54 (270/5)

Conditional Aggregation

Use CASE or FILTER (PostgreSQL) for conditional counting:

SELECT
  COUNT(*) AS total_orders,
  COUNT(CASE WHEN status = 'completed' THEN 1 END) AS completed,
  COUNT(CASE WHEN status = 'cancelled' THEN 1 END) AS cancelled,
  SUM(CASE WHEN status = 'completed' THEN amount ELSE 0 END) AS completed_revenue
FROM orders;

-- PostgreSQL FILTER syntax (cleaner)
SELECT
  COUNT(*) FILTER (WHERE status = 'completed') AS completed,
  SUM(amount) FILTER (WHERE status = 'completed') AS completed_revenue
FROM orders;

Statistical Aggregates

SELECT
  STDDEV(salary) AS salary_stddev,
  VARIANCE(salary) AS salary_variance,
  PERCENTILE_CONT(0.5) WITHIN GROUP (ORDER BY salary) AS median_salary
FROM employees;

Performance Notes

• COUNT(*) is generally faster than COUNT(column) because it does not check for NULLs
• COUNT(DISTINCT) is significantly more expensive, requiring sort or hash operations
• For approximate distinct counts on large datasets, consider HyperLogLog extensions

Aggregate functions are indispensable for analytics, reporting, and any query that summarizes large datasets into meaningful metrics.