Module 15 — Aggregate Functions

1. COUNT — Counting Rows

What it is: COUNT returns the number of rows that match a condition. COUNT(*) counts all rows, while COUNT(column) counts only non-NULL values in that column.

Why we use it: Counting is fundamental to data analysis — "how many users signed up today?", "how many orders were placed?", "how many products are in stock?".

When we use it: In dashboards, reports, analytics, pagination (total count), and any time you need to know "how many".

-- Count all rows
SELECT COUNT(*) FROM users;
 
-- Count non-NULL values in a specific column
SELECT COUNT(email) FROM users;
 
-- Count unique values
SELECT COUNT(DISTINCT category) FROM products;

2. SUM — Totaling Values

What it is: SUM adds up all numeric values in a column. It ignores NULL values and returns the total.

Why we use it: Totals are everywhere in business — total revenue, total inventory, total hours worked. SUM computes these in the database, which is faster than summing in application code.

When we use it: In financial reports, revenue calculations, inventory totals, analytics dashboards, and any time you need the sum of a column.

-- Total revenue from all orders
SELECT SUM(total) FROM orders;
 
-- Total price of electronics
SELECT SUM(price) FROM products WHERE category = 'Electronics';

3. AVG — Calculating Averages

What it is: AVG calculates the arithmetic mean of all numeric values in a column. It ignores NULL values — NULL rows don't affect the average.

Why we use it: Averages are essential for analysis — average order value, average rating, average response time. They help you understand the "typical" value in a dataset.

When we use it: In analytics, performance metrics, pricing analysis, and any time you need the "middle" value of a dataset.

-- Average product price
SELECT AVG(price) FROM products;
 
-- Average age of users
SELECT AVG(age) FROM users;

4. MIN and MAX — Finding Extremes

What it is: MIN returns the smallest value and MAX returns the largest value in a column. They work on numbers, dates, and text (alphabetical order).

Why we use it: Finding extremes is common — cheapest product, most expensive order, earliest record, latest activity. MIN/MAX find these in one query.

When we use it: In price ranges, date ranges, "best/worst" queries, and any time you need the boundary values of a dataset.

-- Cheapest and most expensive product
SELECT MIN(price) AS cheapest, MAX(price) AS most_expensive
FROM products;
 
-- Earliest and latest order
SELECT MIN(created_at) AS first_order, MAX(created_at) AS last_order
FROM orders;

5. Multiple Aggregates in One Query

What it is: You can combine multiple aggregate functions in a single SELECT statement. Each aggregate computes independently across all rows, and they're returned as separate columns.

Why we use it: Instead of running 5 separate queries to get count, sum, avg, min, and max, you can get all of them in one query. This is faster and more efficient.

When we use it: In dashboards and summary reports that need multiple statistics, or any time you need a comprehensive overview of a dataset.

-- Get a summary of product prices
SELECT
    COUNT(*) AS total_products,
    AVG(price) AS average_price,
    MIN(price) AS cheapest,
    MAX(price) AS most_expensive,
    SUM(price) AS total_value
FROM products;

6. How Aggregates Handle NULL

What it is: Different aggregate functions handle NULL values differently. COUNT(*) counts all rows including NULLs, but COUNT(column) and other aggregates (SUM, AVG, MIN, MAX) ignore NULL values.

Why we use it: Understanding NULL behavior prevents bugs — if you use COUNT(bio) expecting to count all users, you'll miss users with NULL bios. Use COUNT(*) for total counts.

When we use it: Every time you use aggregates with nullable columns. This is one of the most common sources of bugs in SQL queries.

-- Example: bio is NULL for some users
SELECT COUNT(*) AS all_users, COUNT(bio) AS users_with_bio FROM users;

Common Mistakes to Avoid

1. Confusing COUNT(*) with COUNT(column) — They behave differently with NULLs
2. Using AVG on integer columns — Integer division truncates. Use AVG(price::NUMERIC) for precise results
3. Forgetting that aggregates collapse rows — You can't mix individual columns with aggregates without GROUP BY

Practice Exercise

Using your courses table:

1. Count the total number of courses
2. Count how many courses have an instructor (non-NULL)
3. Find the average course price
4. Find the cheapest and most expensive course
5. Count how many unique instructors there are
6. Get a full summary: count, average, min, max, and total price