MySQL GROUP BY

The GROUP BY clause in MySQL groups rows that have the same value in one or more columns into a single summary row. It is almost always used with aggregate functions like COUNT(), SUM(), AVG(), MAX(), and MIN() to produce grouped reports.

Why Use GROUP BY?

Without GROUP BY, aggregate functions return a single result for the entire table. GROUP BY allows the calculation of that summary for each group — for example, total sales per region, or number of employees per department.

Syntax

SELECT column_name, aggregate_function(column)
FROM table_name
GROUP BY column_name;

Sample Table

CREATE TABLE sales (
    sale_id INT PRIMARY KEY,
    salesperson VARCHAR(100),
    region VARCHAR(50),
    amount DECIMAL(10,2)
);

INSERT INTO sales VALUES (1, 'Arjun', 'North', 15000);
INSERT INTO sales VALUES (2, 'Priya', 'South', 22000);
INSERT INTO sales VALUES (3, 'Arjun', 'North', 18000);
INSERT INTO sales VALUES (4, 'Meena', 'East',   9500);
INSERT INTO sales VALUES (5, 'Priya', 'South', 31000);
INSERT INTO sales VALUES (6, 'Meena', 'East',  12000);

Example: Total Sales per Region

SELECT region, SUM(amount) AS total_sales
FROM sales
GROUP BY region;

Result:

region | total_sales
-------+------------
North  | 33000.00
South  | 53000.00
East   | 21500.00

Example: Count of Sales per Salesperson

SELECT salesperson, COUNT(*) AS number_of_sales
FROM sales
GROUP BY salesperson;

Result:

salesperson | number_of_sales
------------+----------------
Arjun       | 2
Priya       | 2
Meena       | 2

Example: Average Sale Amount per Region

SELECT region, AVG(amount) AS avg_sale
FROM sales
GROUP BY region;

GROUP BY with Multiple Columns

Grouping by more than one column creates a summary for each unique combination.

SELECT salesperson, region, SUM(amount) AS total
FROM sales
GROUP BY salesperson, region;

Result:

salesperson | region | total
------------+--------+---------
Arjun       | North  | 33000.00
Priya       | South  | 53000.00
Meena       | East   | 21500.00

GROUP BY with ORDER BY

SELECT region, SUM(amount) AS total_sales
FROM sales
GROUP BY region
ORDER BY total_sales DESC;

This displays regions sorted from highest to lowest total sales.

GROUP BY with WHERE

WHERE filters rows before grouping takes place.

SELECT salesperson, SUM(amount) AS total
FROM sales
WHERE region != 'East'
GROUP BY salesperson;

Only North and South rows are grouped; East rows are excluded entirely.

Important Rule

Every column in the SELECT clause that is not inside an aggregate function must appear in the GROUP BY clause. Otherwise, MySQL returns an error (in strict SQL mode).

-- Correct
SELECT region, SUM(amount) FROM sales GROUP BY region;

-- Incorrect (salesperson is not aggregated or grouped)
SELECT salesperson, region, SUM(amount) FROM sales GROUP BY region;

Key Points

• GROUP BY groups rows with matching values into summary rows.
• It is almost always used alongside aggregate functions.
• Multiple columns can be grouped together for more detailed summaries.
• WHERE filters rows before grouping; HAVING filters after grouping.
• All non-aggregated columns in SELECT must appear in GROUP BY.