SQL GROUP BY Clause

The SQL GROUP BY clause is used to group rows that have the same values in specified columns into summary rows, often for use with aggregate functions such as COUNT, SUM, AVG, MIN, or MAX. This clause is especially useful when you want to aggregate data and view results based on unique column values.

In this tutorial, we will guide you through the syntax of SQL GROUP BY clause, and how to use in SQL statements with the help of examples.

Syntax of SQL GROUP BY Clause

The basic syntax of the SQL GROUP BY clause is as follows:

</>

Copy

SELECT column1, aggregate_function(column2)
FROM table_name
GROUP BY column1;

Each part of this syntax has a specific purpose:

SELECT: Specifies the columns to retrieve, including the column used for grouping and any aggregate functions.
FROM: Specifies the table from which to retrieve data.
GROUP BY: Specifies the column(s) used to group the result set.

The GROUP BY clause is often used with aggregate functions to produce summary data. You can also use multiple columns with GROUP BY to create nested groupings.

Step-by-Step Examples with MySQL

We’ll go through various examples demonstrating the GROUP BY clause in MySQL. Using MySQL 8.0 with MySQL Workbench, we’ll use a sample students table with fields id, name, age, grade, and locality.

Setup for Examples: Creating the Database and Table

1. Open MySQL Workbench and create a new database:

</>

Copy

CREATE DATABASE school;

2. Select the school database:

</>

Copy

USE school;

3. Create a table named students:

</>

Copy

CREATE TABLE students (
    id INT PRIMARY KEY AUTO_INCREMENT,
    name VARCHAR(50),
    age INT,
    grade VARCHAR(10),
    locality VARCHAR(50)
);

4. Insert some sample records to use in our examples:

</>

Copy

INSERT INTO students (name, age, grade, locality)
VALUES
('Alice', 14, '8th', 'Northside'),
('Bob', 15, '9th', 'Westend'),
('Charlie', 14, '8th', 'Northside'),
('David', 16, '10th', 'Southend'),
('Eva', 15, '9th', 'Westend');

Examples: Using GROUP BY Clause in Queries

Now, let’s explore different scenarios of using the GROUP BY clause with this table.

TutorialKart

SQL GROUP BY Clause – Syntax, Examples