MySQL Group By

From Rows to Summaries

GROUP BY takes a result set and folds every row that shares the same value in one or more columns into a single row. On its own, grouping just deduplicates values, but it becomes powerful once paired with an aggregate function - COUNT, SUM, AVG, MIN, or MAX - which computes one number per group instead of per row. Where a customer_id column might have 40 order rows scattered across a table, GROUP BY customer_id turns that into one row per customer, and COUNT(*) tells you how many orders each one placed.

SELECT customer_id, COUNT(*) AS order_count
FROM orders
GROUP BY customer_id;

Aggregate Functions You'll Use With GROUP BY

• COUNT(*) counts rows in a group; COUNT(column) counts only the rows where that column isn't NULL.
• SUM(column) adds up a numeric column across the group.
• AVG(column) computes the mean of a numeric column across the group.
• MIN(column) and MAX(column) return the smallest and largest values found in the group.

SELECT department_id,
       COUNT(*) AS employee_count,
       SUM(salary) AS total_salary,
       AVG(salary) AS avg_salary
FROM employees
GROUP BY department_id;

Filtering Groups with HAVING

WHERE filters individual rows before they're grouped, so it can't reference an aggregate like COUNT(*) - at the point WHERE runs, no aggregation has happened yet. HAVING runs after grouping and aggregation, so it can filter on the aggregate results themselves, such as keeping only the departments whose total salary exceeds a threshold. A single query can use both: WHERE to narrow the raw rows first, then HAVING to narrow the resulting groups.

SELECT department_id, SUM(salary) AS total_salary
FROM employees
GROUP BY department_id
HAVING SUM(salary) > 500000;