GROUP BY & HAVING

Aggregate by groups — and filter those groups

← Lab 4: Aggregates Lab 5 of 10 Lab 6: JOINs →

⏳ 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

GROUP BY column — groups rows with the same value, then aggregates each group
Every column in SELECT must either appear in GROUP BY or be inside an aggregate function
HAVING — filters groups after aggregation (like WHERE but for groups)
WHERE filters rows before grouping; HAVING filters groups after aggregating

Full clause order: SELECT → FROM → WHERE → GROUP BY → HAVING → ORDER BY → LIMIT

👀 Worked Example

-- Revenue by product category, only categories with avg price > 100 SELECT p.category, COUNT(*) AS products_sold, ROUND(SUM(o.total), 2) AS category_revenue, ROUND(AVG(p.price), 2) AS avg_price FROM orders o JOIN products p ON o.product_id = p.id GROUP BY p.category HAVING AVG(p.price) > 100 ORDER BY category_revenue DESC;

✏️ Guided

Exercise 1 — Customers with Multiple Orders

Fill in the blanks to find customers who have placed more than 1 order.

-- Customers who have placed more than 1 order
SELECT
    customer_id,
    COUNT(*) AS order_count,
    ROUND(SUM(total), 2) AS lifetime_value
FROM orders
GROUP BY ___
HAVING COUNT(*) > ___
ORDER BY lifetime_value DESC;

💡 Hint: GROUP BY customer_id, HAVING COUNT(*) > 1

💪 Independent

Exercise 2 — Category Sales Volume

Find all product categories where the total quantity sold (sum of quantity from orders joined with products) is greater than 3. Show: category, total quantity sold, total revenue. You’ll need to JOIN orders and products.

-- Categories where total quantity sold > 3

💡 Hint: JOIN orders o ON o.product_id = p.id, then GROUP BY p.category, HAVING SUM(o.quantity) > 3

🔥 Challenge

Exercise 3 — Power Customers

Write a query that finds “power customers” — customers who have spent more than $500 total. Show: customer_id, number of orders, total spent, and average order value. Order by total spent descending.

-- Power customers: total spent > $500

💡 Hint: GROUP BY customer_id, HAVING SUM(total) > 500, then show COUNT(*), SUM(total), ROUND(AVG(total),2)

🏆 Mini Project

Mini Project — Sales Performance Report

Build a complete sales performance report using GROUP BY and HAVING:

Revenue by customer — all customers with their order count and total revenue
High-value customers — only those who spent more than $300 total
Product sales by category — category, number of products in category, times ordered, total revenue
Monthly revenue trend — grouped by month (use substr), only months with total revenue over $500

-- Query 1: Revenue by customer (all)


-- Query 2: High-value customers (> $300 total)


-- Query 3: Product sales performance by category


-- Query 4: Monthly revenue (> $500 months only)

✅ Lab 5 Complete!

GROUP BY and HAVING unlock true analytical power. You can now segment any dataset and filter by group-level conditions. Up next: JOINs — the most powerful concept in SQL.

Continue to Lab 6: JOINs →

← Lab 4: Aggregates Lab 5 of 10 Lab 6: JOINs →