SQL GROUP BY and HAVING for Team Metrics

Control aggregation scope

At Northstar, finance tracks paid order revenue by customer segment each month. The query should filter to paid orders in WHERE, then group by segment and month so the metric aligns with accounting rules.

If you group by too many columns, you can accidentally produce near-raw output and miss the intended summary level.

Use HAVING for threshold questions

HAVING is useful for questions like segments with more than 100 orders. It evaluates after grouping, so it can reference COUNT, SUM, or AVG results.

Keep threshold values in visible parameters when possible. Hard-coded cutoffs are easy to forget and can drift from policy.

• DATE_TRUNC helps group timestamps into month buckets.
• COUNT(DISTINCT ...) can prevent duplicate-key inflation.
• Round only at presentation time to avoid cumulative math drift.

Find high-volume segments in Q1

Orders table fields include order_id, customer_id, segment, order_date, status, and total_amount.

1. Filter WHERE status = 'paid' and order_date between Q1 boundaries.
2. GROUP BY segment and compute COUNT(*) as paid_orders plus SUM(total_amount) as paid_revenue.
3. Apply HAVING COUNT(*) >= 200 to keep only high-volume segments.

Common mistakes

• Using HAVING for row-level filters that belong in WHERE.
• Grouping by display text that changes over time instead of stable codes.
• Comparing rounded aggregates during validation.
• Mixing paid and pending statuses in one revenue metric by mistake.

Try one

Sources

• PostgreSQL 18 Docs: Aggregate FunctionsOfficial behavior and syntax for COUNT, SUM, AVG, and related aggregates.