GROUP BY and HAVING in MySQL

GROUP BY collapses rows that share common values into summary rows, letting you compute aggregates like counts, sums, and averages per group. HAVING then filters those groups, much like WHERE filters individual rows. Together they are the foundation of reporting and analytics queries.

Basic GROUP BY

Every column in the SELECT list that is not inside an aggregate function must appear in the GROUP BY clause. MySQL groups all rows with the same value in those columns and then applies the aggregate to each group.

SELECT department, COUNT(*) AS employee_count
FROM employees
GROUP BY department;

Common Aggregate Functions

MySQL provides several built-in aggregates: COUNT(), SUM(), AVG(),MIN(), and MAX(). You can use multiple aggregates in a single query.

SELECT
  category,
  COUNT(*)       AS total_products,
  AVG(price)     AS avg_price,
  MIN(price)     AS cheapest,
  MAX(price)     AS most_expensive,
  SUM(quantity)  AS total_stock
FROM products
GROUP BY category;

Grouping by Multiple Columns

You can group by more than one column to create finer-grained summaries. MySQL creates a group for each unique combination of the listed columns.

SELECT
  YEAR(order_date)  AS order_year,
  MONTH(order_date) AS order_month,
  SUM(total)        AS monthly_revenue
FROM orders
GROUP BY YEAR(order_date), MONTH(order_date)
ORDER BY order_year, order_month;

Filtering Groups with HAVING

WHERE filters rows before grouping; HAVING filters after. Use HAVING when your condition involves an aggregate function. This distinction is important because the aggregate values do not exist until the groups are formed.

-- Departments with more than 10 employees
SELECT department, COUNT(*) AS headcount
FROM employees
GROUP BY department
HAVING COUNT(*) > 10;

-- Categories where average price exceeds 100
SELECT category, AVG(price) AS avg_price
FROM products
GROUP BY category
HAVING AVG(price) > 100
ORDER BY avg_price DESC;

GROUP BY with ROLLUP

The WITH ROLLUP modifier adds extra summary rows that represent higher-level aggregations. It is useful for generating subtotals and grand totals in a single query.

SELECT
  COALESCE(department, 'ALL DEPARTMENTS') AS department,
  SUM(salary) AS total_salary
FROM employees
GROUP BY department WITH ROLLUP;

Performance Tip

GROUP BY can be expensive on large tables. Make sure the columns you group by are indexed. If you only need approximate counts on very large datasets, consider using COUNT(DISTINCT ...) cautiously and explore summary tables for frequently accessed aggregations.