Learn the usage of GROUP BY clause to group a set of rows in MySQL with and without aggregate functions.

Group Rows Using Group By Clause In MySQL
Group Rows Using Group By Clause In MySQL
September 23, 2019

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.

Write a Comment

Click on the captcha image to get new code.
Discussion Forum by DISQUS