GROUP BY

Intermediate

⏱️ 12 mins read

What You'll Learn

GROUP BY groups rows with identical values into summary rows. Every column in SELECT must either appear in GROUP BY or be wrapped in an aggregate function. You can group by multiple columns to get unique combinations. Execution order: WHERE filters rows → GROUP BY groups → HAVING filters groups.

Syntax

SELECT col, AGG(col2) FROM table
GROUP BY col;

SELECT col1, col2, AGG(col3) FROM table
GROUP BY col1, col2;

Example

-- Headcount per department
SELECT department, COUNT(*) AS headcount
FROM employees
GROUP BY department;

-- Revenue per customer per month
SELECT
  customer_id,
  DATE_FORMAT(order_date, '%Y-%m') AS month,
  COUNT(*) AS orders,
  SUM(total) AS revenue
FROM orders
GROUP BY customer_id, DATE_FORMAT(order_date, '%Y-%m');

Common Mistakes

MySQL (with ONLY_FULL_GROUP_BY disabled) may allow non-grouped columns in SELECT — PostgreSQL and SQL Server do NOT. Always follow the rule: every SELECT column must be in GROUP BY or aggregated.

Interview Tips

Know that GROUP BY can be used without aggregate functions — it acts like DISTINCT. Also: grouping by an expression (DATE_FORMAT, YEAR()) is common in analytics. Mention that NULL values are grouped together.

Practice

Find total sales per product category per month for 2024. Show category, month, total revenue, and order count.

Back to Full Roadmap