GROUP BY / HAVING

-- Group rows by a column and aggregate.
SELECT group_column, aggregate_function FROM table_name GROUP BY group_column;

-- Filter the aggregated results with an additional condition.
SELECT group_column, aggregate_function FROM table_name GROUP BY group_column HAVING aggregate_condition;

-- Use WHERE to filter rows before grouping, and HAVING to filter groups after aggregation.
SELECT group_column, aggregate_function
FROM table_name
WHERE row_filter_condition
GROUP BY group_column
HAVING aggregate_condition
ORDER BY sort_order;

-- Get the number of employees per department.
SELECT department, COUNT(*) AS employee_count
FROM employees
GROUP BY department;

-- Get only departments where the average salary is 300,000 or more (filter after aggregation with HAVING).
SELECT department, AVG(salary) AS avg_salary
FROM employees
GROUP BY department
HAVING AVG(salary) >= 300000;

-- Target only active employees, and get the total salary per department.
-- Use WHERE to exclude former employees before grouping, and HAVING to keep only departments with 2 or more employees.
SELECT department, COUNT(*) AS employee_count, SUM(salary) AS total_salary
FROM employees
WHERE retire_date IS NULL
GROUP BY department
HAVING COUNT(*) >= 2
ORDER BY total_salary DESC;

-- Get the total sales per product category.
SELECT category, SUM(price * quantity) AS total_sales
FROM order_items
GROUP BY category
ORDER BY total_sales DESC;

-- Result of:
-- SELECT department, COUNT(*) AS employee_count, SUM(salary) AS total_salary
-- FROM employees WHERE retire_date IS NULL GROUP BY department HAVING COUNT(*) >= 2
-- ORDER BY total_salary DESC;
-- +------------+----------------+--------------+
-- | department | employee_count | total_salary |
-- +------------+----------------+--------------+
-- | Engineering|              5 |      1500000 |
-- | Sales      |              4 |      1160000 |
-- +------------+----------------+--------------+

The basic syntax of GROUP BY and HAVING is supported across all major databases.

SELECT department, COUNT(*) AS employee_count
FROM employees
GROUP BY department
HAVING COUNT(*) >= 2;

In MySQL, the default sql_mode includes ONLY_FULL_GROUP_BY, which causes an error if you include a column in the SELECT clause that is not listed in the GROUP BY clause. PostgreSQL, Oracle, and SQL Server enforce the same rule strictly. Older versions of MySQL did not enforce this restriction, which could result in non-deterministic values being returned for ungrouped columns.

In PostgreSQL, you can reference SELECT columns by their position number in the GROUP BY clause.

-- PostgreSQL: GROUP BY using column position
SELECT department, COUNT(*) AS employee_count FROM employees GROUP BY 1;

When using GROUP BY, the SELECT clause can only contain columns listed in the GROUP BY clause and aggregate functions such as COUNT, SUM, AVG, MAX, and MIN. Including a non-grouped column without an aggregate function will cause an error (though some MySQL sql_mode settings may allow it, this is not recommended).

The difference between WHERE and HAVING is the timing of evaluation. WHERE filters individual rows before grouping, so aggregate functions cannot be used. HAVING filters after grouping and aggregation, so conditions like COUNT(*) or SUM(salary) are allowed.

The overall execution order of a SELECT statement is: FROM → WHERE → GROUP BY → HAVING → SELECT → ORDER BY → LIMIT. Keeping this order in mind helps you understand which clauses can be used for which conditions.