← Back to Chapters

MySQL GROUP BY

? MySQL GROUP BY

? Quick Overview

The GROUP BY statement groups rows that have the same values into summary rows — for example total quantity, average salary, or count of employees. It is commonly used with aggregate functions such as COUNT(), SUM(), AVG(), MAX(), and MIN().

? Key Concepts

  • Grouping: Combine rows sharing the same value(s) to compute aggregates per group.
  • Aggregates: Functions like SUM(), AVG(), COUNT() operate on groups.
  • HAVING: Use to filter groups after aggregation (WHERE filters rows before grouping).
  • Non-aggregated columns: Columns in SELECT that are not aggregated should be included in GROUP BY.

? Syntax / Theory

? View Code Example 
// Syntax: group rows by one or more columns and apply aggregate functions
SELECT column1, column2, AGGREGATE_FUNCTION(column)
FROM table
GROUP BY column1, column2;

? Code Example(s)

? View Code Example 
// Example: total sales per category and average salary per department
-- Get the total sales for each product category
SELECT category, SUM(sales)
FROM products
GROUP BY category;

-- Get the average salary by department
SELECT department, AVG(salary)
FROM employees
GROUP BY department;
? View Code Example — multiple-grouping & HAVING 
// Example: group by multiple columns and filter groups using HAVING
SELECT product_id, region, SUM(sales) AS total_sales
FROM sales
GROUP BY product_id, region
HAVING SUM(sales) > 1000;

? Live Output / Explanation

Explanation

When you run a GROUP BY query the database scans rows, groups them by the listed column(s), then computes the aggregate(s) for each group. For example, SUM(sales) yields one summed value per distinct category.

Example result (conceptual)

For SELECT category, SUM(sales) FROM products GROUP BY category; you might get:

  • Electronics — 120000
  • Clothing — 54000
  • Home — 78000

? Tips & Best Practices

  • Include all non-aggregated columns from SELECT in the GROUP BY clause.
  • Use HAVING to filter aggregated results (e.g., groups with SUM > threshold).
  • Use indexes on grouping columns for large tables to improve performance when possible.
  • When grouping by expressions (e.g., DATE(created_at)), ensure consistent expression use in SELECT and GROUP BY.

? Try It Yourself / Practice Tasks

  • Write a query to find the total number of orders per customer: SELECT customer_id, COUNT(*) FROM orders GROUP BY customer_id;
  • Find the highest and lowest salaries per department using MAX() and MIN().
  • Group by multiple columns: total sales by product and region, then filter groups using HAVING.