Lesson 4: Aggregate Functions

In Lesson 3, we sorted results with ORDER BY and LIMIT to get the top N rows. This time, we learn aggregate functions that summarize multiple rows into a single number, such as "how many total customers are there" or "what is the average order amount."

Concept: Aggregate functions summarize multiple rows into a single value.

COUNT

COUNT(*) counts the total number of rows in the result. COUNT(column) counts the number of non-NULL values in that column.

-- Total number of customers
SELECT COUNT(*) AS total_customers
FROM customers;

Result:

-- Compare customer counts by birth date registration
SELECT
    COUNT(*)           AS total_customers,
    COUNT(birth_date)  AS with_birth_date,
    COUNT(*) - COUNT(birth_date) AS missing_birth_date
FROM customers;

Result:

COUNT(DISTINCT) -- Unique Value Count

COUNT(DISTINCT column) counts the number of values after removing duplicates. Use it when you want to know "how many distinct types exist."

-- Number of unique customers who have placed orders
SELECT
    COUNT(*)                    AS total_orders,
    COUNT(DISTINCT customer_id) AS unique_customers
FROM orders;

Result:

There are 34,908 orders, but only 4,985 unique customers have placed orders, since one customer can place multiple orders.

-- Number of brands TechShop carries
SELECT COUNT(DISTINCT brand) AS brand_count
FROM products;

SUM

SUM calculates the total of a numeric column. NULL values are ignored.

-- Total revenue from completed orders
SELECT SUM(total_amount) AS total_revenue
FROM orders
WHERE status IN ('delivered', 'confirmed');

Result:

-- Total points held by active customers
SELECT SUM(point_balance) AS total_points_outstanding
FROM customers
WHERE is_active = 1;

Result:

AVG

AVG returns the arithmetic mean, excluding NULL values from the calculation.

-- Average price and average stock of active products
SELECT
    AVG(price)     AS avg_price,
    AVG(stock_qty) AS avg_stock
FROM products
WHERE is_active = 1;

Result:

-- Average order amount excluding cancellations and returns
SELECT AVG(total_amount) AS avg_order_value
FROM orders
WHERE status NOT IN ('cancelled', 'returned');

Result:

ROUND -- Rounding

AVG results can have many decimal places. Use ROUND(value, digits) to round to the desired number of decimal places.

-- Average review rating rounded to 1 decimal place
SELECT
    AVG(rating)          AS avg_raw,
    ROUND(AVG(rating), 1) AS avg_rounded
FROM reviews;

Result:

-- Average product price rounded to the nearest whole number
SELECT ROUND(AVG(price), 0) AS avg_price
FROM products;

Integer Division Caution

In SQLite, integer / integer = integer. The decimal part is truncated:

-- Problem: trying to calculate the review rate...
SELECT
    COUNT(*)                    AS total_orders,
    (SELECT COUNT(*) FROM reviews) AS total_reviews,
    (SELECT COUNT(*) FROM reviews) / COUNT(*) AS review_rate
FROM orders;

7945 / 34908 = 0.2275... but because of integer division, it becomes 0. The fix:

-- Fix: multiply by a float (1.0)
SELECT ROUND((SELECT COUNT(*) FROM reviews) * 1.0 / COUNT(*) * 100, 1) AS review_rate_pct
FROM orders;

MIN and MAX

MIN and MAX find the smallest and largest values in a column.

-- Lowest and highest prices among active products
SELECT
    MIN(price) AS cheapest,
    MAX(price) AS most_expensive
FROM products
WHERE is_active = 1;

Result:

-- First and most recent order dates
SELECT
    MIN(ordered_at) AS first_order,
    MAX(ordered_at) AS latest_order
FROM orders;

Result:

Using Multiple Aggregate Functions Together

You can use multiple aggregate functions in a single SELECT.

-- TechShop review statistics summary
SELECT
    COUNT(*)                    AS total_reviews,
    AVG(rating)                 AS avg_rating,
    MIN(rating)                 AS lowest_rating,
    MAX(rating)                 AS highest_rating,
    SUM(CASE WHEN rating = 5 THEN 1 ELSE 0 END) AS five_star_count
FROM reviews;

Result:

Aggregate Functions and NULL

Aggregate functions silently ignore NULL values. This is an important behavior:

-- About 15% of customers have NULL birth_date
SELECT
    COUNT(*)           AS total,
    COUNT(birth_date)  AS with_birth,
    AVG(CASE
        WHEN birth_date IS NOT NULL
        THEN 2025 - CAST(SUBSTR(birth_date, 1, 4) AS INTEGER)
    END) AS avg_age
FROM customers;

• COUNT(*) = 5,230 (all rows including NULL)
• COUNT(birth_date) = 4,450 (excluding NULL)
• AVG = calculated only for the 4,450 with data (780 with NULL are excluded)

Summary

Practice Problems

Problem 1

Calculate the average review rating from the reviews table, rounded to 2 decimal places. Use the alias avg_rating.

SELECT ROUND(AVG(rating), 2) AS avg_rating
FROM reviews;

Problem 2

Calculate the total revenue (sum of total_amount) from completed orders (status is 'delivered' or 'confirmed') in the orders table. Use the alias total_revenue.

SELECT SUM(total_amount) AS total_revenue
FROM orders
WHERE status IN ('delivered', 'confirmed');

Problem 3

From the customers table, find the total number of customers and the number of customers with a registered birth_date. Use the aliases total_customers and with_birth_date.

SELECT
    COUNT(*)          AS total_customers,
    COUNT(birth_date) AS with_birth_date
FROM customers;

Problem 4

Count the number of currently active products at TechShop and calculate their total inventory value (sum of price * stock_qty).

SELECT
    COUNT(*)                AS active_product_count,
    SUM(price * stock_qty)  AS total_inventory_value
FROM products
WHERE is_active = 1;

Problem 5

Calculate the average, minimum, and maximum total_amount for orders that were not cancelled or returned. Use the aliases avg_order, min_order, and max_order.

SELECT
    AVG(total_amount) AS avg_order,
    MIN(total_amount) AS min_order,
    MAX(total_amount) AS max_order
FROM orders
WHERE status NOT IN ('cancelled', 'returned', 'return_requested');

Problem 6

From the products table, calculate the average price (avg_price, 0 decimal places), average cost (avg_cost, 0 decimal places), and average margin rate (avg_margin_pct, 1 decimal place) of active products in a single query. Margin rate = (price - cost_price) / price * 100, and you should compute the average of each product's margin rate.

SELECT
    ROUND(AVG(price), 0)                          AS avg_price,
    ROUND(AVG(cost_price), 0)                           AS avg_cost,
    ROUND(AVG((price - cost_price) / price * 100), 1)   AS avg_margin_pct
FROM products
WHERE is_active = 1;

Problem 7

From the products table, find the minimum price, maximum price, and price range for active products (is_active = 1). Use the aliases min_price, max_price, and price_range.

SELECT
    MIN(price)             AS min_price,
    MAX(price)             AS max_price,
    MAX(price) - MIN(price) AS price_range
FROM products
WHERE is_active = 1;

Problem 8

From the order_items table, find the total row count, total quantity sum (quantity), average unit price (unit_price, 2 decimal places), and maximum quantity. Use the aliases total_items, total_qty, avg_unit_price, and max_qty.

SELECT
    COUNT(*)                    AS total_items,
    SUM(quantity)               AS total_qty,
    ROUND(AVG(unit_price), 2)   AS avg_unit_price,
    MAX(quantity)               AS max_qty
FROM order_items;

Problem 9

From the payments table, find the count, total amount, average amount (0 decimal places), and minimum/maximum amounts for completed payments (status = 'completed') in a single query. Use the aliases payment_count, total_amount, avg_amount, min_amount, and max_amount.

SELECT
    COUNT(*)              AS payment_count,
    SUM(amount)           AS total_amount,
    ROUND(AVG(amount), 0) AS avg_amount,
    MIN(amount)           AS min_amount,
    MAX(amount)           AS max_amount
FROM payments
WHERE status = 'completed';

Problem 10

How many orders have a shipping note (notes), and what percentage of total orders is that? Return orders_with_notes, total_orders, and pct_with_notes (1 decimal place).

SELECT
    COUNT(CASE WHEN notes IS NOT NULL THEN 1 END)  AS orders_with_notes,
    COUNT(*)                                        AS total_orders,
    ROUND(
        100.0 * COUNT(CASE WHEN notes IS NOT NULL THEN 1 END) / COUNT(*),
        1
    ) AS pct_with_notes
FROM orders;

Scoring Guide

Problem Areas:

Next: Lesson 5: GROUP BY and HAVING