Group Rows Using Group By Clause In MySQL

This tutorial is part of the series Learn Basic SQL Queries Using MySQL. In this tutorial, we will discuss SQL queries to perform grouping of the table rows in MySQL using the GROUP BY clause with the aggregate functions. You can also follow Using Aggregate Functions (SUM, AVG, MAX, MIN, COUNT, DISTINCT) in MySQL to learn the aggregate functions.

Group By Query

The GROUP BY clause can be used to perform grouping of the table rows. It can also be used with the aggregate functions to generate the reports and to collect statistical data.

# GROUP BY - Syntax
SELECT column_1, column_2, ..., [<aggregate function> AS <column>] FROM `table_name` GROUP BY column_1, column_2, ...;

Query Explanation

The GROUP BY clause can be used to group the rows by specifying the columns to form the set of rows.

We can use aggregate functions to effectively use GROUP BY clause to generate the reports and to collect statistical data.

The grouping can be applied on multiple columns where the first column will form the primary group and the subsequent columns will form sub-groups.

Examples

This section provides examples to perform grouping using the GROUP BY clause with and without aggregation functions. Use the below-mentioned query to create the user table having the id, first name, last name, age, and active columns to store user data.

# Create the User Table
CREATE TABLE `enterprise`.`user` (
  `user_id` BIGINT NOT NULL,
  `first_name` VARCHAR(45) ,
  `last_name` VARCHAR(45),
  `age` SMALLINT(6) NOT NULL DEFAULT 0,
  `active` TINYINT(1) NOT NULL DEFAULT 0,
  PRIMARY KEY (`user_id`));

The below-mentioned query can be used to insert test data in the user table.

# Insert Rows - All Columns
INSERT INTO `user` ( `user_id`, `first_name`, `last_name`, `age`, `active` ) VALUES 
    ( 1, 'John', 'Smith', 18, 1 ),
    ( 2, 'Rick', 'Jones', 19, 1 ),
    ( 3, 'John', 'Ponting', 16, 0 ),
    ( 4, 'Harsh', 'Upadhyay', 20, 1 ),
    ( 5, 'Tajwinder', 'Singh', 18, 0 );

The above-mentioned query will insert 5 rows in the table having the id, first name, last name, and active columns to represent 5 different users.

Now we will use the GROUP BY clause without the aggregate functions to group the rows. It can be done using the GROUP BY clause in MySQL as shown below.

# GROUP BY - Generate the set of active values
SELECT active from user GROUP BY active;

# Result
1
0

# GROUP BY - Generate the set of first name
SELECT first_name from user GROUP BY first_name;

# Result
John
Rick
Harsh
Tajwinder

The above-mentioned queries returned the result set by grouping the rows using the GROUP BY clause. Now we will group the rows with the aggregate function as shown below.

# GROUP BY - Get the maximum age of all users with specific first name
SELECT first_name, MAX(age) AS max_age from user GROUP BY first_name;

# Result
John		18
Rick		19
Harsh		20
Tajwinder	18

# GROUP BY - Get the average age of all users with specific first name
SELECT first_name, AVG(age) AS avg_age from user GROUP BY first_name;

# Result
John		17.0000
Rick		19.0000
Harsh		20.0000
Tajwinder	18.0000

This is how we can use the GROUP BY clause with and without the aggregate functions to group the rows of the table for the specific columns.