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.

SQL GROUP BY - Example Table

1. Grouping by a Single Column with COUNT

To count the number of students in each grade:

</>
Copy
SELECT grade, COUNT(*) AS student_count
FROM students
GROUP BY grade;

This query groups students by grade and counts the number of students in each grade, displaying the count under student_count.

SQL GROUP BY - Single Column with COUNT

2. Grouping by Multiple Columns

To count the number of students in each grade by locality:

</>
Copy
SELECT grade, locality, COUNT(*) AS student_count
FROM students
GROUP BY grade, locality;

This query groups students by both grade and locality, displaying the number of students in each grade-locality combination.

SQL GROUP BY Multiple Columns

3. Using GROUP BY with AVG

To find the average age of students in each grade:

</>
Copy
SELECT grade, AVG(age) AS avg_age
FROM students
GROUP BY grade;

This query calculates the average age of students in each grade by grouping on the grade column.

SQL Using GROUP BY with AVG

4. Using GROUP BY with HAVING Clause

The HAVING clause filters groups after aggregation, allowing you to restrict the groups that appear in the results. To find grades with more than one student:

</>
Copy
SELECT grade, COUNT(*) AS student_count
FROM students
GROUP BY grade
HAVING COUNT(*) > 1;

This query only displays grades with more than one student, filtering the results based on the aggregate count.

SQL GROUP BY with HAVING Clause

FAQs for SQL GROUP BY

1. What does the SQL GROUP BY clause do?

The GROUP BY clause groups rows with the same values in specified columns and is often used with aggregate functions to summarize data.

2. Can I use multiple columns with GROUP BY?

Yes, you can group by multiple columns. Each unique combination of values from the grouped columns forms a separate group in the result set.

3. What is the difference between WHERE and HAVING with GROUP BY?

The WHERE clause filters rows before grouping, while HAVING filters groups after aggregation, allowing you to set conditions on aggregate results.

4. Can I use GROUP BY without an aggregate function?

Yes, you can use GROUP BY without an aggregate function, though typically it’s used alongside aggregates to summarize data.

5. How do I count unique values with GROUP BY?

To count unique values, use COUNT(DISTINCT column) with GROUP BY. This counts unique occurrences of a column’s values within each group.