Grouping Data with GROUP BY0%

Grouping Data with GROUP BY

Beginner12 min read•Updated: 2026-09-12

Grouping Data with GROUP BY: Multi-Dimensional Aggregation

Calculating the average salary across an entire company is helpful, but management usually needs more granular breakdowns: "What is the average salary in each department?", "How much revenue did each product category generate this month?", or "How many orders did each customer place?". The GROUP BY clause collapses rows sharing identical values into summary rows, enabling multi-dimensional aggregations.

1. Syntax of the `GROUP BY` Clause

1SELECT grouping_column, AGGREGATE_FUNCTION(target_column)
2FROM table_name
3[WHERE filter_condition]
4GROUP BY grouping_column
5[ORDER BY sort_column];

Step-by-Step Example:

1SELECT 
2    department,
3    COUNT(*) AS employee_count,
4    AVG(salary) AS average_salary,
5    MAX(salary) AS highest_salary
6FROM employees
7GROUP BY department
8ORDER BY average_salary DESC;

How MySQL Processes `GROUP BY`:

1
WHERE: Filters individual rows first.
2
GROUP BY: Gathers remaining rows into buckets based on distinct department values.
3
Aggregate Functions: Computes COUNT, AVG, and MAX for each departmental bucket independently.
4
SELECT: Projects the results.
5
ORDER BY: Sorts the aggregated buckets.

2. Multi-Column Grouping

You can group by multiple columns simultaneously to create hierarchical breakdowns:

1-- Sales revenue broken down by Country AND City:
2SELECT 
3    country,
4    city,
5    COUNT(order_id) AS total_orders,
6    SUM(order_total) AS total_revenue
7FROM orders
8GROUP BY country, city
9ORDER BY country ASC, total_revenue DESC;

3. The `ONLY_FULL_GROUP_BY` Standard in MySQL 8.0

In MySQL 5.7+, the ONLY_FULL_GROUP_BY SQL mode is enabled by default. This enforces strict standard SQL compliance:

[!CRITICAL] The Golden Rule of GROUP BY: Any column listed in the SELECT clause that is NOT wrapped inside an aggregate function MUST appear in the GROUP BY clause!

Example of an Invalid Query:

1-- ILLEGAL under ONLY_FULL_GROUP_BY:
2SELECT department, full_name, AVG(salary) 
3FROM employees 
4GROUP BY department;

Why does this fail?

Because a single department (e.g., Engineering) might have 50 employees with 50 different names! Which name should the database pick for the single summary row? In standard SQL, this ambiguity is rejected with error 1055.

4. Grouping by Expressions

You can group by date expressions or calculated columns:

1-- Group sales by Month:
2SELECT 
3    DATE_FORMAT(order_date, '%Y-%m') AS sales_month,
4    SUM(total_amount) AS monthly_revenue
5FROM orders
6GROUP BY DATE_FORMAT(order_date, '%Y-%m')
7ORDER BY sales_month ASC;

5. Best Practices & Common Pitfalls

Do Not Group by Unindexed High-Cardinality Columns: Grouping by columns containing millions of distinct values (like UUIDs or timestamps) forces MySQL to build massive temporary hash tables in memory or on disk.
Index Your GROUP BY Columns: An index covering the grouped column allows the storage engine to read groups in pre-aggregated order without filesort.

Interactive Quiz• Knowledge Check

Multiple Choice Questions

Question 1

What does the GROUP BY clause do in a SQL query?

Question 2

Under MySQL's default ONLY_FULL_GROUP_BY SQL mode, which columns can appear in the SELECT list?

Question 3

What will happen if you execute SELECT department, employee_name, MAX(salary) FROM staff GROUP BY department; in MySQL 8.0?

Question 4

Can you group records by multiple columns simultaneously in MySQL?

Question 5

In what logical order does the query engine evaluate WHERE vs GROUP BY?

Next Lesson

Filtering Groups with HAVING vs WHERE

Continue learning with hands-on practice, examples, and exercises in the upcoming topic.

Related Lessons

Previous Lesson	Next Lesson
Aggregate Functions: COUNT, SUM, AVG, MIN, MAX	Filtering Groups with HAVING vs WHERE

Practice Quiz

Test your understanding of this lesson with 5 questions. Each question has one correct answer.

Previous Next

PreviousPrevNext

MSK Institute • Shikohabad

Loading Page Content...

Preparing courses, live labs, and learning resources.सामग्री लोड हो रही है, कृपया प्रतीक्षा करें...

Connecting serverPlease wait...

💻 Practical Labs🚀 Free Demo📜 Verified Certificates

Filtering Groups with HAVING vs WHERE

Grouping Data with GROUP BY

Beginner12 min read•Updated: 2026-09-12

Study Materials Cheatsheet

Grouping Data with GROUP BY: Multi-Dimensional Aggregation

1. Syntax of the `GROUP BY` Clause

1SELECT grouping_column, AGGREGATE_FUNCTION(target_column)
2FROM table_name
3[WHERE filter_condition]
4GROUP BY grouping_column
5[ORDER BY sort_column];

Step-by-Step Example:

1SELECT 
2    department,
3    COUNT(*) AS employee_count,
4    AVG(salary) AS average_salary,
5    MAX(salary) AS highest_salary
6FROM employees
7GROUP BY department
8ORDER BY average_salary DESC;

How MySQL Processes `GROUP BY`:

1
WHERE: Filters individual rows first.
2
GROUP BY: Gathers remaining rows into buckets based on distinct department values.
3
Aggregate Functions: Computes COUNT, AVG, and MAX for each departmental bucket independently.
4
SELECT: Projects the results.
5
ORDER BY: Sorts the aggregated buckets.

2. Multi-Column Grouping

You can group by multiple columns simultaneously to create hierarchical breakdowns:

1-- Sales revenue broken down by Country AND City:
2SELECT 
3    country,
4    city,
5    COUNT(order_id) AS total_orders,
6    SUM(order_total) AS total_revenue
7FROM orders
8GROUP BY country, city
9ORDER BY country ASC, total_revenue DESC;

3. The `ONLY_FULL_GROUP_BY` Standard in MySQL 8.0

In MySQL 5.7+, the ONLY_FULL_GROUP_BY SQL mode is enabled by default. This enforces strict standard SQL compliance:

[!CRITICAL] The Golden Rule of GROUP BY: Any column listed in the SELECT clause that is NOT wrapped inside an aggregate function MUST appear in the GROUP BY clause!

Example of an Invalid Query:

1-- ILLEGAL under ONLY_FULL_GROUP_BY:
2SELECT department, full_name, AVG(salary) 
3FROM employees 
4GROUP BY department;

Why does this fail?

4. Grouping by Expressions

You can group by date expressions or calculated columns:

1-- Group sales by Month:
2SELECT 
3    DATE_FORMAT(order_date, '%Y-%m') AS sales_month,
4    SUM(total_amount) AS monthly_revenue
5FROM orders
6GROUP BY DATE_FORMAT(order_date, '%Y-%m')
7ORDER BY sales_month ASC;

5. Best Practices & Common Pitfalls

Do Not Group by Unindexed High-Cardinality Columns: Grouping by columns containing millions of distinct values (like UUIDs or timestamps) forces MySQL to build massive temporary hash tables in memory or on disk.
Index Your GROUP BY Columns: An index covering the grouped column allows the storage engine to read groups in pre-aggregated order without filesort.

Interactive Quiz• Knowledge Check

Multiple Choice Questions

Question 1

What does the GROUP BY clause do in a SQL query?

Question 2

Under MySQL's default ONLY_FULL_GROUP_BY SQL mode, which columns can appear in the SELECT list?

Question 3

What will happen if you execute SELECT department, employee_name, MAX(salary) FROM staff GROUP BY department; in MySQL 8.0?

Question 4

Can you group records by multiple columns simultaneously in MySQL?

Question 5

In what logical order does the query engine evaluate WHERE vs GROUP BY?

Next Lesson

Filtering Groups with HAVING vs WHERE

Continue learning with hands-on practice, examples, and exercises in the upcoming topic.

Related Lessons

Previous Lesson	Next Lesson
Aggregate Functions: COUNT, SUM, AVG, MIN, MAX	Filtering Groups with HAVING vs WHERE

Practice Quiz

Test your understanding of this lesson with 5 questions. Each question has one correct answer.

Previous Next

Grouping Data with GROUP BY: Multi-Dimensional Aggregation

1. Syntax of the GROUP BY Clause

Step-by-Step Example:

How MySQL Processes GROUP BY:

2. Multi-Column Grouping

3. The ONLY_FULL_GROUP_BY Standard in MySQL 8.0

Example of an Invalid Query:

Why does this fail?

4. Grouping by Expressions

5. Best Practices & Common Pitfalls

Multiple Choice Questions

Filtering Groups with HAVING vs WHERE

Related Lessons

Practice Quiz

Loading Page Content...

Grouping Data with GROUP BY: Multi-Dimensional Aggregation

1. Syntax of the GROUP BY Clause

Step-by-Step Example:

How MySQL Processes GROUP BY:

2. Multi-Column Grouping

3. The ONLY_FULL_GROUP_BY Standard in MySQL 8.0

Example of an Invalid Query:

Why does this fail?

4. Grouping by Expressions

5. Best Practices & Common Pitfalls

Multiple Choice Questions

Filtering Groups with HAVING vs WHERE

Related Lessons

Practice Quiz

1. Syntax of the `GROUP BY` Clause

How MySQL Processes `GROUP BY`:

3. The `ONLY_FULL_GROUP_BY` Standard in MySQL 8.0

1. Syntax of the `GROUP BY` Clause

How MySQL Processes `GROUP BY`:

3. The `ONLY_FULL_GROUP_BY` Standard in MySQL 8.0