MySQL - Group By Clause

MySQL: The GROUP BY Clause

What is the GROUP BY Clause?

The GROUP BY clause in MySQL is used to group rows that have the same values in specified columns into summary rows. It is an essential tool for data analysis and reporting, allowing you to perform aggregate calculations on subsets of your data.

For instance, in a sales table, you could group by product_category to find the total sales for each category. GROUP BY is almost always used in conjunction with aggregate functions like COUNT(), SUM(), AVG(), MIN(), and MAX() to produce meaningful summary information.

The GROUP BY clause is placed after the WHERE clause and before the HAVING or ORDER BY clauses.

General Syntax

SELECT
    column_name1,
    aggregate_function(column_name2)
FROM
    table_name
WHERE
    condition
GROUP BY
    column_name1
HAVING
    aggregate_condition
ORDER BY
    column_name1;

Example Setup

We will use the following CUSTOMERS table for our examples:

CREATE TABLE CUSTOMERS(
    ID INT PRIMARY KEY AUTO_INCREMENT,
    NAME VARCHAR(100) NOT NULL,
    AGE INT NOT NULL,
    ADDRESS VARCHAR(255),
    SALARY DECIMAL(10, 2)
);

INSERT INTO CUSTOMERS (NAME, AGE, ADDRESS, SALARY) VALUES
('Ramesh', 32, 'Ahmedabad', 2000.00),
('Khilan', 25, 'Delhi', 1500.00),
('Kaushik', 23, 'Kota', 2000.00),
('Chaitali', 25, 'Mumbai', 6500.00),
('Hardik', 27, 'Bhopal', 8500.00),
('Komal', 22, 'Hyderabad', 4500.00),
('Muffy', 24, 'Indore', 10000.00);

GROUP BY on a Single Column with Aggregate Functions

The most common use of GROUP BY is to group by a single column and apply an aggregate function.

Example: Count Customers by Age

Let’s find out how many customers there are for each age group.

SELECT
    AGE,
    COUNT(*) AS NumberOfCustomers
FROM
    CUSTOMERS
GROUP BY
    AGE;

Output

AGE	NumberOfCustomers
32	1
25	2
23	1
27	1
22	1
24	1

Example: Calculate Average Salary by Age

Now let’s find the average salary for each age group.

SELECT
    AGE,
    AVG(SALARY) AS AverageSalary
FROM
    CUSTOMERS
GROUP BY
    AGE;

Output

AGE	AverageSalary
32	2000.00
25	4000.00
23	2000.00
27	8500.00
22	4500.00
24	10000.00

GROUP BY on Multiple Columns

You can also group by multiple columns to create more granular summaries. The database will create a unique group for each combination of values from the specified columns.

Example: Group by Address and Age

To illustrate, let’s add a duplicate record and group by ADDRESS and AGE.

INSERT INTO CUSTOMERS (NAME, AGE, ADDRESS, SALARY) VALUES ('Riya', 25, 'Mumbai', 7000.00);

SELECT
    ADDRESS,
    AGE,
    COUNT(*) AS CountInGroup
FROM
    CUSTOMERS
GROUP BY
    ADDRESS, AGE;

The result will show a count for each unique (ADDRESS, AGE) pair. For instance, ('Mumbai', 25) will now have a count of 2.

Filtering Groups with the HAVING Clause

The WHERE clause filters rows before they are grouped. If you want to filter the results after grouping, based on the result of an aggregate function, you must use the HAVING clause.

Example: Find Age Groups with More Than One Customer

SELECT
    AGE,
    COUNT(*) AS NumberOfCustomers
FROM
    CUSTOMERS
GROUP BY
    AGE
HAVING
    COUNT(*) > 1;

Output

AGE	NumberOfCustomers
25	2

Interaction with ORDER BY

You can use ORDER BY to sort the final grouped results. By default, the order of grouped results is not guaranteed.

SELECT
    AGE,
    AVG(SALARY) AS AverageSalary
FROM
    CUSTOMERS
GROUP BY
    AGE
ORDER BY
    AverageSalary DESC;

This query will list the age groups sorted from the highest average salary to the lowest.

Understanding ONLY_FULL_GROUP_BY

Modern versions of MySQL enable the ONLY_FULL_GROUP_BY SQL mode by default. This is a crucial concept. It means that any column in your SELECT list that is not part of an aggregate function must be included in the GROUP BY clause. This prevents ambiguity in the results.

For example, the following query is invalid with this mode enabled because NAME is not in an aggregate function and not in the GROUP BY clause. Which NAME should MySQL show for the age group ‘25’ that has two people?

-- This query will fail in modern MySQL by default
SELECT NAME, AGE, COUNT(*) FROM CUSTOMERS GROUP BY AGE;

To get a list of names within each group, you can use the GROUP_CONCAT() function:

SELECT
    AGE,
    COUNT(*) AS NumberOfCustomers,
    GROUP_CONCAT(NAME) AS CustomerNames
FROM
    CUSTOMERS
GROUP BY
    AGE;