Aggregations, GROUP BY, and HAVING

Concept

Aggregations summarize many rows into a metric. COUNT, SUM, AVG, MIN, and MAX answer questions such as how many orders occurred, how much revenue they generated, and what a typical order value was. GROUP BY calculates those summaries separately for each group.

Why It Matters

Analytics work is often a comparison: revenue by region, orders by status, or average order value by customer segment. A grouped query turns transaction-level records into a compact business view without losing the definition of the metric.

Aggregate Carefully

SELECT
  COUNT(*) AS order_rows,
  COUNT(revenue) AS orders_with_revenue,
  SUM(revenue) AS total_revenue,
  AVG(revenue) AS average_order_value
FROM orders
WHERE status = 'completed';

COUNT(*) counts rows, including rows where a column is NULL. COUNT(revenue) counts only rows with a non-null revenue value. The expected result is one summary row, so inspect both counts before treating an average as complete.

GROUP BY

region belongs to customers, so revenue by region requires a join:

SELECT
  c.region,
  SUM(o.revenue) AS total_revenue,
  COUNT(*) AS completed_orders
FROM customers AS c
INNER JOIN orders AS o
  ON c.customer_id = o.customer_id
WHERE o.status = 'completed'
GROUP BY c.region
ORDER BY total_revenue DESC;

region	total_revenue
West	128500
East	94700

Each output row is now one region. Every selected expression must either appear in GROUP BY or be aggregated. Grouping by region, status creates one row per region-status combination.

HAVING Versus WHERE

WHERE filters rows before grouping. HAVING filters groups after an aggregate exists.

SELECT
  status,
  COUNT(*) AS order_count
FROM orders
WHERE order_date >= '2025-01-01'
GROUP BY status
HAVING COUNT(*) > 100
ORDER BY order_count DESC;

This first keeps 2025 rows, then counts each status, then retains statuses with more than 100 orders. Use WHERE for row conditions and HAVING for summary conditions.

Best Practices

Name metrics with aliases, check null counts, and validate the grain of each result: one row per region, month, or customer segment. Compare totals before and after joins. For wide business differences, show counts alongside rates or averages.

Data Science Perspective

Grouped SQL creates features, experiment summaries, dashboards, and extracts for Python. A customer-level dataset might aggregate each customer's completed orders, total revenue, and most recent order date before modeling churn.

Interview Perspective

Question: What is the difference between WHERE and HAVING? Answer: WHERE filters input rows before aggregation; HAVING filters the resulting groups. Follow-up: explain COUNT(*) versus COUNT(column).

Practice Questions

1. Find total completed revenue by month.
2. Return customer segments with more than 50 completed orders.
3. Fix a query that selects region, order_date, SUM(revenue) but groups only by region.

Quick Quiz

1. Which clause creates one result row per group? Answer: GROUP BY.
2. Does COUNT(column) include null values? Answer: no.
3. When should HAVING be used? Answer: to filter aggregated groups.

Mental Model: Rows to Groups to Metrics

Imagine completed order rows flowing into the query. WHERE first removes rows outside the cohort. GROUP BY then places the remaining rows into buckets, such as one bucket per region. SUM, AVG, MIN, MAX, and COUNT turn each bucket into metrics, and HAVING decides which metric rows stay. This sequence explains why a revenue condition belongs in WHERE, while SUM(revenue) > 10000 belongs in HAVING.

SELECT
  c.segment,
  o.status,
  COUNT(*) AS order_count,
  AVG(o.revenue) AS average_revenue
FROM customers AS c
JOIN orders AS o ON o.customer_id = c.customer_id
WHERE o.status = 'completed'
GROUP BY c.segment, o.status
HAVING COUNT(*) >= 25;

This has one output row per segment-status pair. In this example status is always completed because of WHERE, but including it demonstrates multiple grouping columns. In real reporting, remove redundant grouping fields. Always inspect COUNT(*) beside AVG(revenue): an average from three orders should not be interpreted with the confidence of one from three thousand.

Next Lesson

Next, combine related tables safely with SQL joins.