Aggregate Functions

Explanation

Aggregate functions take a column of many values and crunch it down to one summary value. The five workhorses are:

• COUNT — how many rows (or non-null values)
• SUM — total of a numeric column
• AVG — average of a numeric column
• MIN — smallest value
• MAX — largest value

Used alone, they return a single row for the whole table. Paired with GROUP BY (see the GROUP BY lesson), they compute one summary per group.

Syntax

SELECT
  COUNT(*) AS total_rows,
  SUM(price) AS total_value,
  AVG(price) AS avg_price,
  MIN(price) AS cheapest,
  MAX(price) AS priciest
FROM table_name;

Interactive Example

Summarize the entire products table. Then compute per-category aggregates with GROUP BY.

Common Mistakes

• Confusing COUNT(*) with COUNT(column). They differ whenever NULLs exist.
• Forgetting GROUP BY when mixing columns. SELECT category, AVG(price) without GROUP BY category is invalid in strict databases.
• Averaging the wrong thing. AVG(quantity) and AVG(price * quantity) answer very different business questions.

Best Practices

• Use COUNT(*) when you want a row count; use COUNT(col) to count populated values.
• Round money with ROUND(..., 2) so output stays clean.
• Combine aggregates with GROUP BY to turn a single summary into a full report.

Practice Question

Using orders, return the customer_id with the highest total quantity ordered. Hint: SUM(quantity) grouped by customer_id, then order and LIMIT 1.

Summary

Aggregate functions collapse many rows into one value. COUNT(*) counts rows, COUNT(col) counts non-nulls, and SUM/AVG/MIN/MAX summarize numbers — all ignoring NULLs except COUNT(*).

FAQ