GROUP BY in SQL

The GROUP BY clause combines rows having the same value into groups.

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

This query counts students separately for each course.

GROUP BY is used when you need summary information for different categories.

For example:

• Number of students in each course
• Total sales for each product
• Average marks for each class
• Total salary for each department
• Number of employees in each department

The basic syntax is:

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

Example:

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

COUNT() can be used with GROUP BY to count records in each group.

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

This shows the number of students in every course.

SUM() can calculate a total separately for every group.

SELECT course, SUM(fee) AS total_fee
FROM students
GROUP BY course;

This calculates the total fee for each course.

AVG() can calculate the average value for each group.

SELECT course, AVG(marks) AS average_marks
FROM students
GROUP BY course;

This calculates the average marks for each course.

MIN() can find the smallest value in each group.

SELECT course, MIN(marks) AS lowest_marks
FROM students
GROUP BY course;

This returns the lowest marks for each course.

MAX() can find the largest value in each group.

SELECT course, MAX(marks) AS highest_marks
FROM students
GROUP BY course;

This returns the highest marks for each course.

You can use several aggregate functions in the same query.

SELECT
    course,
    COUNT(*) AS total_students,
    AVG(marks) AS average_marks,
    MIN(marks) AS lowest_marks,
    MAX(marks) AS highest_marks
FROM students
GROUP BY course;

This generates a complete summary for every course.

WHERE filters rows before they are grouped.

SELECT course, COUNT(*) AS total_students
FROM students
WHERE status = 'Active'
GROUP BY course;

This counts only active students in each course.

ORDER BY can be used after GROUP BY to sort grouped results.

SELECT course, COUNT(*) AS total_students
FROM students
GROUP BY course
ORDER BY total_students DESC;

The courses with more students appear first.

You can group data using more than one column.

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

This creates groups based on the combination of course and status.

Multiple columns allow more detailed summaries.

SELECT department, gender, COUNT(*) AS total_employees
FROM employees
GROUP BY department, gender;

This counts employees for each department and gender combination.

GROUP BY naturally produces one result row for each group.

SELECT course
FROM students
GROUP BY course;

This returns each course once.

If the grouped column contains NULL values, those NULL values can form their own group.

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

A group can therefore represent rows where the course value is NULL.

IN can be used in WHERE before grouping.

SELECT course, COUNT(*) AS total_students
FROM students
WHERE course IN ('Python', 'SQL')
GROUP BY course;

This groups only Python and SQL students.

BETWEEN can filter records before GROUP BY is applied.

SELECT course, AVG(marks) AS average_marks
FROM students
WHERE marks BETWEEN 50 AND 100
GROUP BY course;

This calculates the average marks for qualifying records in each course.

LIKE can be used to filter records before grouping.

SELECT course, COUNT(*) AS total_students
FROM students
WHERE course LIKE 'P%'
GROUP BY course;

This groups courses whose names start with P.

GROUP BY can be used with date-related columns to summarize records.

SELECT admission_date, COUNT(*) AS total_students
FROM students
GROUP BY admission_date;

This counts students for each admission date.

In MySQL, functions such as YEAR() can be used to group records by year.

SELECT
    YEAR(admission_date) AS admission_year,
    COUNT(*) AS total_students
FROM students
GROUP BY YEAR(admission_date);

This provides the number of students admitted in each year.

You can also group records by month using appropriate date functions.

SELECT
    MONTH(admission_date) AS admission_month,
    COUNT(*) AS total_students
FROM students
GROUP BY MONTH(admission_date);

This counts students for each month number.

Aliases can make grouped results easier to understand.

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

The output columns have meaningful names.

GROUP BY does not itself calculate totals or averages. Aggregate functions perform the calculations for each group.

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

Here, GROUP BY creates the course groups and COUNT() calculates the number of rows in each group.

GROUP BY is very useful for payment reports.

SELECT
    course,
    SUM(amount) AS total_payment
FROM payments
WHERE status = 'Paid'
GROUP BY course;

This calculates paid amounts separately for each course.

You can calculate salary summaries for each department.

SELECT
    department,
    SUM(salary) AS total_salary,
    AVG(salary) AS average_salary
FROM employees
GROUP BY department;

This shows total and average salary for each department.

Grouped results can be sorted by an aggregate value.

SELECT
    course,
    COUNT(*) AS total_students
FROM students
GROUP BY course
ORDER BY total_students DESC;

This sorts courses from the highest student count to the lowest.

A common mistake is selecting a non-aggregated column that is not properly included in GROUP BY.

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

Depending on the SQL mode and database system, this query may be invalid or produce an unintended result. When grouping, make sure selected non-aggregated columns are appropriately included in GROUP BY.

• GROUP BY creates groups of similar values.
• ORDER BY sorts the result.

SELECT course, COUNT(*) AS total_students
FROM students
GROUP BY course
ORDER BY total_students DESC;

Here GROUP BY creates the groups and ORDER BY sorts them.

A practical student report can show the number of students and average marks for every course.

SELECT
    course,
    COUNT(*) AS total_students,
    AVG(marks) AS average_marks,
    MIN(marks) AS lowest_marks,
    MAX(marks) AS highest_marks
FROM students
GROUP BY course
ORDER BY total_students DESC;

This provides a useful course-wise student performance summary.

Here is a complete example using GROUP BY with filtering, aggregate functions, and sorting.

CREATE TABLE students (
    student_id INT PRIMARY KEY,
    name VARCHAR(100),
    course VARCHAR(100),
    status VARCHAR(20),
    marks INT,
    fee DECIMAL(10,2)
);

SELECT
    course,
    COUNT(*) AS total_students,
    SUM(fee) AS total_fee,
    AVG(marks) AS average_marks,
    MIN(marks) AS lowest_marks,
    MAX(marks) AS highest_marks
FROM students
WHERE status = 'Active'
GROUP BY course
ORDER BY total_students DESC;

This query creates a course-wise report containing the number of active students, total fees, average marks, lowest marks, and highest marks.