GROUP BY and HAVING

Aggregation is what turns raw transaction records into business metrics. GROUP BY collapses rows into groups and lets you compute totals, averages, and counts per group. HAVING filters those groups. Together, they answer questions like "which customers spent the most?" and "which products have low sell-through rates?"

Learning Objectives

• Group rows with GROUP BY and compute aggregates
• Use COUNT, SUM, AVG, MIN, MAX correctly
• Filter aggregated results with HAVING
• Understand the difference between WHERE (pre-aggregation) and HAVING (post-aggregation)
• Use CASE WHEN inside aggregates

---

Aggregate Functions

Before GROUP BY, you need to know the aggregate functions:

Function	Returns	Example
COUNT(*)	Number of rows	COUNT(*) → total orders
COUNT(column)	Non-NULL values in column	COUNT(delivery_date) → delivered orders
COUNT(DISTINCT col)	Unique non-NULL values	COUNT(DISTINCT customer_id) → unique buyers
SUM(column)	Sum of values	SUM(order_total) → total revenue
AVG(column)	Average of values	AVG(unit_price) → mean product price
MIN(column)	Smallest value	MIN(order_date) → earliest order
MAX(column)	Largest value	MAX(order_total) → biggest single order

---

GROUP BY Basics

GROUP BY splits rows into groups. The aggregate function then runs on each group separately.

-- How many orders per status?
SELECT
    status,
    COUNT(*)    AS order_count
FROM orders
GROUP BY status
ORDER BY order_count DESC;

Sample output:

status	order_count
completed	42
pending	18
cancelled	9
refunded	3

-- Revenue per customer
SELECT
    customer_id,
    COUNT(*)            AS total_orders,
    SUM(order_total)    AS total_revenue,
    AVG(order_total)    AS avg_order_value,
    MAX(order_total)    AS largest_order
FROM orders
WHERE status = 'completed'
GROUP BY customer_id
ORDER BY total_revenue DESC;

Sample output:

customer_id	total_orders	total_revenue	avg_order_value	largest_order
C003	12	2195.50	182.96	389.00
C001	9	1456.25	161.81	298.00
C008	6	894.00	149.00	149.00

---

The GROUP BY Rule

Every column in SELECT must either be in GROUP BY, or be inside an aggregate function.

-- WRONG: customer_id is in GROUP BY, but first_name is not
SELECT customer_id, first_name, COUNT(*)
FROM orders
GROUP BY customer_id;

-- CORRECT option 1: add first_name to GROUP BY
GROUP BY customer_id, first_name;

-- CORRECT option 2: use a JOIN to get first_name after aggregation
SELECT o.customer_id, c.first_name, COUNT(*) AS order_count
FROM orders o
JOIN customers c ON o.customer_id = c.customer_id
GROUP BY o.customer_id, c.first_name;

MySQL's non-standard GROUP BY behaviour

MySQL in some modes allows SELECT columns that aren't in GROUP BY and aren't aggregated — it picks an arbitrary row's value. This looks like it works but produces random results. PostgreSQL (strict mode) and SQL Server reject this query entirely. Always follow the rule: GROUP BY or aggregate.

---

Grouping by Multiple Columns

-- Revenue by customer AND by month
SELECT
    customer_id,
    DATE_FORMAT(order_date, '%Y-%m')    AS order_month,   -- MySQL
    -- TO_CHAR(order_date, 'YYYY-MM')   AS order_month,   -- PostgreSQL
    COUNT(*)                            AS orders,
    SUM(order_total)                    AS revenue
FROM orders
WHERE status = 'completed'
GROUP BY customer_id, DATE_FORMAT(order_date, '%Y-%m')
ORDER BY customer_id, order_month;

---

HAVING — Filtering Groups

WHERE filters rows before aggregation. HAVING filters groups after aggregation.

-- Customers with more than 5 completed orders
SELECT
    customer_id,
    COUNT(*)    AS order_count
FROM orders
WHERE status = 'completed'        -- filter rows first
GROUP BY customer_id
HAVING COUNT(*) > 5               -- then filter groups
ORDER BY order_count DESC;

Rule to remember

WHERE goes before GROUP BY. HAVING goes after GROUP BY. If the condition involves an aggregate function (COUNT, SUM, AVG), it must go in HAVING.

-- Products generating more than £500 in revenue
SELECT
    product_id,
    COUNT(*)            AS times_ordered,
    SUM(order_total)    AS total_revenue
FROM orders
WHERE status = 'completed'
GROUP BY product_id
HAVING SUM(order_total) > 500
ORDER BY total_revenue DESC;

Wrong way:

-- WRONG: can't use aggregate function in WHERE
WHERE SUM(order_total) > 500;   -- syntax error

---

COUNT(*) vs COUNT(column)

SELECT
    COUNT(*)                AS total_rows,
    COUNT(delivery_date)    AS delivered_orders,    -- NULLs not counted
    COUNT(DISTINCT customer_id) AS unique_customers
FROM orders;

Sample output:

total_rows	delivered_orders	unique_customers
72	49	28

COUNT(*) counts all rows including those with NULLs. COUNT(column) counts only non-NULL values in that column.

For mid-level analysts

Use COUNT(*) when you want total rows. Use COUNT(column) when you want to count non-NULL values (e.g., how many orders have been delivered). Use COUNT(DISTINCT column) when you want unique values.

---

CASE WHEN Inside Aggregates

CASE WHEN lets you write conditional logic. Inside an aggregate, it's a powerful pattern for pivoting data.

-- Count orders by status in a single row (pivot)
SELECT
    COUNT(*)                                        AS total_orders,
    COUNT(CASE WHEN status = 'completed' THEN 1 END)    AS completed,
    COUNT(CASE WHEN status = 'pending' THEN 1 END)      AS pending,
    COUNT(CASE WHEN status = 'cancelled' THEN 1 END)    AS cancelled,
    ROUND(
        COUNT(CASE WHEN status = 'completed' THEN 1 END) * 100.0 / COUNT(*), 1
    )   AS completion_rate_pct
FROM orders;

Sample output:

total_orders	completed	pending	cancelled	completion_rate_pct
72	42	18	9	58.3

-- Revenue split by customer segment
SELECT
    c.segment,
    COUNT(DISTINCT o.customer_id)               AS customers,
    SUM(o.order_total)                          AS revenue,
    AVG(o.order_total)                          AS avg_order_value
FROM orders o
JOIN customers c ON o.customer_id = c.customer_id
WHERE o.status = 'completed'
GROUP BY c.segment
ORDER BY revenue DESC;

---

Real Business Example — Monthly Revenue Report

SELECT
    DATE_FORMAT(order_date, '%Y-%m')    AS month,
    COUNT(*)                            AS orders,
    COUNT(DISTINCT customer_id)         AS unique_customers,
    SUM(order_total)                    AS gross_revenue,
    AVG(order_total)                    AS avg_order_value,
    MIN(order_total)                    AS min_order,
    MAX(order_total)                    AS max_order
FROM orders
WHERE status = 'completed'
GROUP BY DATE_FORMAT(order_date, '%Y-%m')
ORDER BY month;

Sample output:

month	orders	unique_customers	gross_revenue	avg_order_value	min_order	max_order
2024-01	18	12	4829.50	268.31	35.99	389.00
2024-02	24	16	6145.00	255.96	29.99	478.00

---

Practice Exercises

Warm-up 1. Count the number of orders per status. 2. Calculate total revenue per product. 3. Find the average unit_price per product category.

Main 4. Find the top 5 customers by total revenue (completed orders only). Show customer ID, order count, and total revenue. 5. For each month in 2024, calculate total orders and total revenue. 6. Find product categories where the average order value is above £150. 7. Count how many orders each customer placed. Show only customers with MORE than 3 orders.

Stretch 8. Write a single query that calculates for each customer: total orders, completed orders, cancelled orders, and the cancellation rate as a percentage. Sort by cancellation rate descending. 9. Find months where revenue dropped compared to the previous month. (Hint: this needs a subquery or window function — preview 04-window-functions.)

---

Interview Questions

[Beginner] What is the difference between WHERE and HAVING?

Show answer

WHERE filters individual rows before grouping. HAVING filters groups after aggregation.

Use WHERE when the condition involves column values. Use HAVING when the condition involves an aggregate (COUNT, SUM, AVG, etc.).

SELECT customer_id, COUNT(*) AS orders
FROM orders
WHERE status = 'completed'   -- filter rows before grouping
GROUP BY customer_id
HAVING COUNT(*) > 5;         -- filter groups after grouping

[Beginner] What is the difference between COUNT(*) and COUNT(column_name)?

[Mid-level] A colleague writes SELECT customer_id, first_name, COUNT(*) FROM orders GROUP BY customer_id. In PostgreSQL this throws an error. Why? How do you fix it?

[Mid-level] Write a query that calculates the percentage of orders that are completed vs. total orders, without using a subquery.

[Senior] You have a sales table with 200 million rows. A GROUP BY region, month query takes 5 minutes. What are your options to speed it up?

---

← JOINs · Next: Subqueries and CASE WHEN →