`GROUP BY` Clause in DBMS (MySQL) [ English ]

Languages

• English

Introduction

In a Database Management System (DBMS), data stored in tables may contain many records. Sometimes users need to organize data into groups to perform calculations such as counting records, finding averages, or calculating totals.

The GROUP BY clause is used to group rows that have the same values in specified columns. It is often used together with aggregate functions like COUNT(), SUM(), AVG(), MIN(), and MAX().

Definition

The GROUP BY clause is used in SQL to group rows that share the same values in specified columns and perform aggregate operations on those groups.

Syntax

SELECT column_name, aggregate_function(column_name)
FROM table_name
GROUP BY column_name;

Where:

• column_name – The column used for grouping records.
• aggregate_function() – A function used to perform calculations on grouped data.

Common Aggregate Functions

Example Table

Suppose we have a table named students.

Example 1: Counting Students in Each Course

SELECT course, COUNT(*) 
FROM students
GROUP BY course;

Output

This query groups records by course and counts the number of students in each course.

Example 2: Finding Average Age by Course

SELECT course, AVG(age)
FROM students
GROUP BY course;

Output

This query calculates the average age of students in each course.

Using GROUP BY with Multiple Columns

It is possible to group data based on more than one column.

SELECT column1, column2, COUNT(*)
FROM table_name
GROUP BY column1, column2;

Example:

SELECT course, age, COUNT(*)
FROM students
GROUP BY course, age;

This groups students by both course and age.

Important Notes

• GROUP BY is used with aggregate functions.

• All columns in the SELECT list must either be:

  • Included in the GROUP BY clause, or
  • Used inside an aggregate function.

• The GROUP BY clause usually appears after the WHERE clause and before ORDER BY.

Example:

SELECT course, COUNT(*)
FROM students
WHERE age > 19
GROUP BY course;

Key Points

• GROUP BY is used to group rows with the same values.
• It is commonly used with aggregate functions.
• It helps summarize large datasets.
• Multiple columns can be used for grouping.

Summary

The GROUP BY clause is used in SQL to organize data into groups based on one or more columns. It is commonly used with aggregate functions to perform calculations such as counting records, finding averages, or computing totals. This makes it a powerful tool for summarizing and analyzing data stored in a database.