The GROUP BY clause is used to group rows that have the same values into summary rows. It is commonly used with aggregate functions such as COUNT(), SUM(), AVG(), MAX(), and MIN().
The GROUP BY clause groups rows that have the same value in one or more columns.
SELECT course
FROM students
GROUP BY course;This returns one row for each different course.
GROUP BY is useful when you want summarized information for different categories.
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 rows in each group.
SELECT
course,
COUNT(*) AS total_students
FROM students
GROUP BY course;This returns the number of students enrolled in each course.
SUM() can be used to calculate the total value for each group.
SELECT
course,
SUM(fee) AS total_fee
FROM students
GROUP BY course;This calculates the total fee for every course.
AVG() can calculate the average value for every group.
SELECT
course,
AVG(fee) AS average_fee
FROM students
GROUP BY course;Each course will have its own average fee.
MAX() can find the highest value in each group.
SELECT
course,
MAX(fee) AS highest_fee
FROM students
GROUP BY course;This returns the highest fee for each course.
MIN() can find the lowest value in each group.
SELECT
course,
MIN(fee) AS lowest_fee
FROM students
GROUP BY course;This returns the lowest fee for each course.
You can use several aggregate functions in the same GROUP BY query.
SELECT
course,
COUNT(*) AS total_students,
SUM(fee) AS total_fee,
ROUND(AVG(fee), 2) AS average_fee,
MAX(fee) AS highest_fee,
MIN(fee) AS lowest_fee
FROM students
GROUP BY course;This creates a complete course-wise summary.
WHERE filters rows before GROUP BY creates groups.
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 to sort grouped results.
SELECT
course,
COUNT(*) AS total_students
FROM students
GROUP BY course
ORDER BY total_students DESC;This displays courses from the highest number of students to the lowest.
You can group by more than one column.
SELECT
course,
gender,
COUNT(*) AS total_students
FROM students
GROUP BY course, gender;This creates groups based on the combination of course and gender.
Multiple columns can be used for detailed reports.
SELECT
course,
city,
COUNT(*) AS total_students
FROM students
GROUP BY course, city;This shows the number of students for each course and city combination.
GROUP BY automatically produces one group for each distinct combination of the grouped columns.
SELECT course
FROM students
GROUP BY course;This returns each course once.
For simply listing unique values, DISTINCT can also be used:
SELECT DISTINCT course
FROM students;Date functions can be used with GROUP BY to create time-based reports.
SELECT
YEAR(admission_date) AS admission_year,
COUNT(*) AS total_students
FROM students
GROUP BY YEAR(admission_date);This creates a year-wise student report.
You can group records by month using MONTH().
SELECT
MONTH(payment_date) AS payment_month,
SUM(amount) AS total_collection
FROM payments
GROUP BY MONTH(payment_date);This calculates total payment collection for each month number.
For reports covering multiple years, group by both year and month.
SELECT
YEAR(payment_date) AS payment_year,
MONTH(payment_date) AS payment_month,
SUM(amount) AS total_collection
FROM payments
GROUP BY
YEAR(payment_date),
MONTH(payment_date)
ORDER BY
payment_year,
payment_month;This prevents payments from the same month in different years from being combined.
Aliases make grouped results easier to understand.
SELECT
course AS course_name,
COUNT(*) AS student_count
FROM students
GROUP BY course;The result will contain descriptive column names.
If a grouped column contains NULL values, MySQL groups those NULL values together.
SELECT
city,
COUNT(*) AS total_students
FROM students
GROUP BY city;If some students have no city value, they can appear together in a NULL group.
You can group using an expression or calculated value.
SELECT
YEAR(admission_date) AS admission_year,
COUNT(*) AS total_students
FROM students
GROUP BY YEAR(admission_date);The grouping is based on the year extracted from admission_date.
HAVING filters groups after GROUP BY and aggregation.
SELECT
course,
COUNT(*) AS total_students
FROM students
GROUP BY course
HAVING COUNT(*) > 5;This returns only courses containing more than five students.
HAVING will be covered in detail in the next lesson.
| WHERE | HAVING |
|---|---|
| Filters individual rows | Filters groups |
| Applied before GROUP BY | Applied after GROUP BY |
| Commonly used with regular columns | Commonly used with aggregate results |
Example:
SELECT
course,
COUNT(*) AS total_students
FROM students
WHERE status = 'Active'
GROUP BY course
HAVING COUNT(*) > 5;You can filter groups according to their total value.
SELECT
course,
SUM(fee) AS total_fee
FROM students
GROUP BY course
HAVING SUM(fee) > 50000;This displays only courses whose total fee is greater than 50000.
HAVING can also filter groups according to their average.
SELECT
course,
AVG(marks) AS average_marks
FROM results
GROUP BY course
HAVING AVG(marks) >= 70;This returns courses whose average marks are at least 70.
GROUP BY can be used after joining multiple tables.
SELECT
c.course_name,
COUNT(s.student_id) AS total_students
FROM courses c
LEFT JOIN students s
ON c.id = s.course_id
GROUP BY c.course_name;This can show the number of students enrolled in each course.
A practical fee report can be created using GROUP BY.
SELECT
course,
COUNT(*) AS students,
SUM(total_fee) AS total_fee,
SUM(paid_fee) AS paid_fee,
SUM(total_fee - paid_fee) AS total_due
FROM students
GROUP BY course;This creates a course-wise fee summary.
You can group payments by student.
SELECT
student_id,
COUNT(*) AS payment_count,
SUM(amount) AS total_paid
FROM payments
GROUP BY student_id;This shows how many payments each student has made and the total amount paid.
Grouped results can be sorted using ORDER BY.
SELECT
course,
COUNT(*) AS total_students
FROM students
GROUP BY course
ORDER BY total_students DESC;You can also sort by course name:
SELECT
course,
COUNT(*) AS total_students
FROM students
GROUP BY course
ORDER BY course ASC;A simplified logical order for a grouped query is:
FROM
WHERE
GROUP BY
HAVING
SELECT
ORDER BYFor example:
SELECT
course,
COUNT(*) AS total_students
FROM students
WHERE status = 'Active'
GROUP BY course
HAVING COUNT(*) > 5
ORDER BY total_students DESC;Understanding this order helps you decide where each condition belongs.
CREATE TABLE students (
student_id INT AUTO_INCREMENT PRIMARY KEY,
name VARCHAR(100),
course VARCHAR(100),
city VARCHAR(100),
fee DECIMAL(10,2),
status VARCHAR(20)
);
INSERT INTO students
(name, course, city, fee, status)
VALUES
('Amit Kumar', 'Python', 'Patna', 15000, 'Active'),
('Priya Singh', 'Java', 'Patna', 18000, 'Active'),
('Rahul Sharma', 'Python', 'Gaya', 12000, 'Active'),
('Neha Kumari', 'PHP', 'Patna', 10000, 'Inactive'),
('Ravi Kumar', 'Python', 'Gaya', 15000, 'Active'),
('Pooja Singh', 'Java', 'Patna', 16000, 'Active');
-- Course-wise student count
SELECT
course,
COUNT(*) AS total_students
FROM students
GROUP BY course;
-- Course-wise total fee
SELECT
course,
SUM(fee) AS total_fee
FROM students
GROUP BY course;
-- Course-wise average fee
SELECT
course,
ROUND(AVG(fee), 2) AS average_fee
FROM students
GROUP BY course;
-- City-wise student count
SELECT
city,
COUNT(*) AS total_students
FROM students
GROUP BY city;
-- Active students by course
SELECT
course,
COUNT(*) AS active_students
FROM students
WHERE status = 'Active'
GROUP BY course;
-- Courses having more than one active student
SELECT
course,
COUNT(*) AS active_students
FROM students
WHERE status = 'Active'
GROUP BY course
HAVING COUNT(*) > 1
ORDER BY active_students DESC;This practical example demonstrates course-wise, city-wise, and status-wise grouping along with aggregate functions, WHERE, HAVING, and ORDER BY.
Question: Which SQL clause is used to group rows with the same values?