MYSQL Tutorial

MySQL GROUP BY & HAVING

The GROUP BY clause groups rows that share a value in one or more columns, allowing you to aggregate data per group. Use HAVING to filter groups based on aggregate conditions.

Syntax

SELECT column1, AGG_FUNC(column2)
FROM table_name
WHERE …          -- optional row filter
GROUP BY column1
HAVING condition  -- filter on aggregate
ORDER BY …;      -- optional sorting
  

Example: Basic GROUP BY

-- Total sales per product
SELECT product_id, SUM(quantity) AS total_sold
FROM order_items
GROUP BY product_id;
  

Example: GROUP BY with HAVING

-- Products with more than 100 units sold
SELECT product_id, SUM(quantity) AS total_sold
FROM order_items
GROUP BY product_id
HAVING SUM(quantity) > 100;
  

Example: Multiple Columns & Ordering

-- Average salary per department, sorted descending
SELECT department, AVG(salary) AS avg_salary
FROM employees
GROUP BY department
HAVING AVG(salary) > 50000
ORDER BY avg_salary DESC;
  

Key Points

• All non-aggregated columns in SELECT must appear in GROUP BY (unless using functional dependencies).
• HAVING filters after grouping; WHERE filters before grouping.
• You can group by multiple columns by listing them comma-separated.
• Combine GROUP BY with ROLLUP for subtotals (MySQL 8.0+).