++++Build grouped reports with GROUP BY, filter grouped results with HAVING, and avoid common aggregate query mistakes.
9. GROUP BY and HAVING
GROUP BY and HAVING
GROUP BY creates one result row per group. It is how you answer questions like "How many orders did each customer place?" or "What is revenue by status?"
Group rows by one column
SELECT
status,
COUNT(*) AS order_count,
SUM(total_amount) AS total_value
FROM orders
GROUP BY status;The result has one row for each status.
Group rows by multiple columns
SELECT
customer_id,
status,
COUNT(*) AS order_count,
SUM(total_amount) AS total_value
FROM orders
GROUP BY customer_id, status;This creates one group for each unique (customer_id, status) pair.
WHERE vs HAVING
WHERE filters rows before grouping. HAVING filters groups after aggregation.
Use WHERE to include only paid rows before calculating totals:
SELECT
customer_id,
SUM(total_amount) AS paid_total
FROM orders
WHERE status = "paid"
GROUP BY customer_id;Use HAVING to keep only groups whose aggregate value passes a condition:
SELECT
customer_id,
SUM(total_amount) AS paid_total
FROM orders
WHERE status = "paid"
GROUP BY customer_id
HAVING paid_total >= 500;Sort grouped results
SELECT
customer_id,
COUNT(*) AS order_count
FROM orders
GROUP BY customer_id
ORDER BY order_count DESC;Common mistakes
Every selected column must either be grouped or aggregated.
Bad pattern:
SELECT customer_id, order_date, SUM(total_amount)
FROM orders
GROUP BY customer_id;order_date is not grouped and not aggregated, so it does not have a clear value for each customer group.
Better pattern:
SELECT
customer_id,
MIN(order_date) AS first_order_date,
MAX(order_date) AS latest_order_date,
SUM(total_amount) AS total_amount
FROM orders
GROUP BY customer_id;When a query reads like a report, GROUP BY is usually involved.