MySQL GROUP BY Statement

MySQL GROUP BY Statement

The GROUP BY statement in MySQL is used to group rows that have the same values in specified columns and apply aggregate functions to each group.

•  It is the backbone of reports, analytics, and interview questions.

 What is GROUP BY?

• Groups rows with same column values

• Used with aggregate functions:
  COUNT(), SUM(), AVG(), MIN(), MAX()

• Returns one row per group

 Without GROUP BY, aggregates work on the entire table.

 Basic Syntax

 Simple Example

Count Employees in Each Department

•  One row per department

 GROUP BY with SUM()

Total Salary by Department

 GROUP BY with AVG()

Average Marks by Class

 GROUP BY with Multiple Columns

•  Groups by unique combinations of department + gender

 GROUP BY with WHERE & HAVING

Execution order:

1. WHERE

2. GROUP BY

3. HAVING

4. SELECT

5. ORDER BY

GROUP BY with ORDER BY

GROUP BY with Alias (MySQL Feature)

•  Alias allowed in HAVING (MySQL-specific convenience)

ONLY_FULL_GROUP_BY Mode (Interview)

In strict SQL mode, every non-aggregated column in SELECT must appear in GROUP BY.

 Wrong:

 Correct:

GROUP BY vs DISTINCT

 Common Mistakes

•  Using columns in SELECT not in GROUP BY
•  Using WHERE for aggregate conditions
•  Forgetting HAVING
•  Misunderstanding execution order

Interview Questions & MCQs (Very Important)

Q1. What is GROUP BY used for?

A) Sorting rows
B) Grouping rows
C) Filtering rows
D) Joining tables

Answer: B

Q2. Which functions are used with GROUP BY?

A) Aggregate functions
B) String functions
C) Date functions
D) Control functions

Answer: A

Q3. Which clause filters groups?

A) WHERE
B) GROUP BY
C) HAVING
D) ORDER BY

Answer: C

Q4. Which clause executes first?

A) GROUP BY
B) WHERE

Answer: B

Q5. Can GROUP BY have multiple columns?

A) Yes
B) No

Answer: A

Q6. In strict mode, non-aggregated columns must appear in:

A) SELECT
B) HAVING
C) GROUP BY
D) ORDER BY

Answer: C

 Real-Life Use Cases

•  Sales reports
• Department-wise salary
•  Student performance analysis
•  Customer segmentation
•  Business dashboards

Summary

• GROUP BY groups rows for aggregation

• Used with aggregate functions

• Supports multiple columns

• Works with WHERE, HAVING, ORDER BY

• Crucial for reports, analytics & interviews