SQL GROUP BY with HAVING Clause

SQL GROUP BY with HAVING Clause

GROUP BY collapses rows with identical values in specified columns into summary rows, enabling aggregate calculations. HAVING filters groups after aggregation, similar to how WHERE filters individual rows.

Basic Syntax

SELECT department, COUNT(*) AS employee_count, AVG(salary) AS avg_salary
FROM employees
GROUP BY department
HAVING COUNT(*) >= 5;

How GROUP BY Works

1. The database groups rows that share the same values in the GROUP BY columns
2. Aggregate functions (COUNT, SUM, AVG, MIN, MAX) compute a single value per group
3. Non-aggregated columns in SELECT must appear in GROUP BY (standard SQL requirement)

WHERE vs HAVING

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

SELECT category, SUM(revenue) AS total_revenue
FROM sales
WHERE sale_date >= '2025-01-01'
GROUP BY category
HAVING SUM(revenue) > 10000;

Multi-Column Grouping

SELECT year, quarter, region, SUM(sales) AS total
FROM revenue
GROUP BY year, quarter, region
ORDER BY year, quarter;

Each unique combination of year, quarter, and region forms a separate group.

Grouping with Expressions

SELECT
  EXTRACT(YEAR FROM order_date) AS order_year,
  EXTRACT(MONTH FROM order_date) AS order_month,
  COUNT(*) AS order_count
FROM orders
GROUP BY EXTRACT(YEAR FROM order_date), EXTRACT(MONTH FROM order_date);

Common Pitfalls

• Including columns in SELECT that are not in GROUP BY or aggregate functions causes errors in strict SQL mode
• Using HAVING without GROUP BY applies the condition to the entire result as one group
• Placing non-aggregate conditions in HAVING instead of WHERE hurts performance because all rows must be processed before filtering

GROUP BY and HAVING are foundational for reporting, analytics, and any query that requires summarizing data by categories.