Aggregate Functions (COUNT, SUM, AVG, MIN, MAX)

📌 Definition:

📖 Detailed Explanation:

Aggregates collapse many rows into one summary number, which is exactly what many validations need: does the order total equal the sum of its line items, how many rows did a batch job create, what is the maximum value in a column after a migration. A key subtlety is NULL handling: COUNT(*) counts all rows including those with NULLs, whereas COUNT(column) counts only rows where that column is non-null, and SUM/AVG ignore NULLs entirely. COUNT(DISTINCT column) counts unique non-null values — useful for checking how many distinct users placed orders. AVG can surprise you when NULLs are present, because it averages only the non-null values, not treating NULL as zero. Aggregates are frequently combined with WHERE (to summarize a subset) and, in the next topic, GROUP BY (to summarize per category).

🔑 Key Points:

• COUNT(*) counts all rows; COUNT(col) counts non-null values only
• COUNT(DISTINCT col) counts unique non-null values
• SUM and AVG ignore NULLs — AVG divides by the non-null count, not all rows
• MIN/MAX find boundary values, useful after migrations or batch jobs
• Combine with WHERE to summarize a filtered subset

🌍 Real-World Example:

📎 Code Example:

-- How many orders, and how many distinct customers ordered?
SELECT COUNT(*)               AS order_count,
       COUNT(DISTINCT user_id) AS customers
FROM orders
WHERE created_at >= '2026-07-21';

-- Revenue and average order value for a period
SELECT SUM(total) AS revenue,
       AVG(total) AS avg_order_value,
       MIN(total) AS smallest,
       MAX(total) AS largest
FROM orders
WHERE status = 'confirmed';

-- COUNT(*) vs COUNT(col): the difference is the NULLs
SELECT COUNT(*) AS all_rows, COUNT(country) AS with_country
FROM users;