Aggregate Functions

COUNT, SUM, AVG, MIN, MAX — turning rows into insights

← Lab 3: ORDER BY Lab 4 of 10 Lab 5: GROUP BY →

⏳ Loading SQL engine...

📋 DATABASE SCHEMA — Available Tables

TABLE: products (id, name, category, price, stock) TABLE: customers (id, name, city, email, member_since) TABLE: orders (id, customer_id, product_id, quantity, order_date, total)

📖 Concept Recap

Aggregate functions collapse multiple rows into a single value:

COUNT(*) — count all rows; COUNT(column) — count non-null values
SUM(column) — total of all values
AVG(column) — arithmetic mean
MIN(column) — smallest value
MAX(column) — largest value

Use AS to give your aggregated columns meaningful names. Use ROUND(value, 2) to round decimals. Without GROUP BY, aggregates apply to the whole table and return one row.

Add GROUP BY category to get one row per category (we dig deep into GROUP BY in Lab 5).

👀 Worked Example

-- Overall order statistics SELECT COUNT(*) AS total_orders, SUM(total) AS total_revenue, ROUND(AVG(total), 2) AS avg_order_value, MIN(total) AS smallest_order, MAX(total) AS largest_order FROM orders; -- Product count and average price by category SELECT category, COUNT(*) AS product_count, ROUND(AVG(price), 2) AS avg_price FROM products GROUP BY category;

✏️ Guided

Exercise 1 — Revenue Summary

Fill in the blanks to get total orders, total revenue, and average order value from the orders table.

-- Total revenue and order count
SELECT
    ___(*)  AS total_orders,
    ___(total) AS total_revenue,
    ROUND(___(total), 2) AS avg_order_value
FROM orders;

💡 Hint: The three functions are COUNT, SUM, and AVG — in that order.

💪 Independent

Exercise 2 — Inventory Overview

Write a query showing:

Total number of products (COUNT)
Total inventory value: SUM(price * stock)
Most expensive product price (MAX)
Cheapest product price (MIN)
Average product price (AVG, rounded to 2 decimal places)

-- Inventory overview

💡 Hint: For inventory value: SUM(price * stock) AS total_inventory_value

🔥 Challenge

Exercise 3 — Monthly Order Stats

Write a query showing order statistics by month. Use substr(order_date, 1, 7) to extract the year-month (e.g., “2024-01”). Show: month, order count, total revenue, average order value. Order by month.

-- Order statistics by month

💡 Hint:

SELECT substr(order_date, 1, 7) AS month, COUNT(*) AS orders, ... FROM orders GROUP BY substr(order_date, 1, 7) ORDER BY month;

🏆 Mini Project

Mini Project — Quarterly Business Review

You’re a business analyst presenting a quarterly review. Write 4 aggregate queries:

Overall metrics — total orders, total revenue, total customers (from customers table), average order value
Product performance by category — category, product count, avg price, total stock
Customer engagement — total customers, total orders, average orders per customer (use subquery or just divide the counts)
High-value orders summary — count, total, and average of orders where total > 500

-- Query 1: Overall business metrics


-- Query 2: Product performance by category


-- Query 3: Customer engagement


-- Query 4: High-value orders (total > 500)

✅ Lab 4 Complete!

Aggregate functions are how analysts answer “how many?”, “how much?”, and “what’s the average?” questions. Next, learn to slice those aggregates into groups.

Continue to Lab 5: GROUP BY & HAVING →

← Lab 3: ORDER BY Lab 4 of 10 Lab 5: GROUP BY →