Database Management System (DBMS) / SQL(STRUCTURED QUERY LANGUAGE)

Grouping the Data using GROUP BY

The GROUP BY clause groups records that share common values so we can apply aggregate functions like:

COUNT(), SUM(), AVG(), MAX(), MIN() etc.

⚙️ Base Setup (ONE Code Block)

We will use THIS data for all examples below ⬇️

-- Students Table & Data
CREATE TABLE students (
    id INT AUTO_INCREMENT PRIMARY KEY,
    name VARCHAR(50),
    grade VARCHAR(2),
    section VARCHAR(1),
    marks INT
);

INSERT INTO students (name, grade, section, marks) VALUES
('Raj', 'B', 'X', 78),
('Tina', 'B', 'X', 82),
('Anya', 'A', 'Y', 90),
('Sara', 'A', 'Y', 88),
('John', 'A', 'X', 85),
('Neha', 'C', 'Z', 70);

📌 We will use ONLY this table and data for all examples below

No repeated CREATE TABLE or INSERT again.

📌 COUNT() → Number of Students per Grade

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

📌 COUNT() with WHERE (filter before grouping)

SELECT grade, COUNT(*) AS total_students
FROM students
WHERE grade IN ('A','B')
GROUP BY grade;

📌 GROUP BY Multiple Columns

SELECT grade, section, COUNT(*) AS student_count
FROM students
GROUP BY grade, section;

📌 HAVING → Filter After Grouping

SELECT grade, COUNT(*) AS total_students
FROM students
GROUP BY grade
HAVING COUNT(*) > 1;

📌 SUM() → Total Marks per Grade

SELECT grade, SUM(marks) AS total_marks
FROM students
GROUP BY grade;

📌 AVG() → Average Marks per Grade

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

📌 MAX() → Highest Marks per Grade

SELECT grade, MAX(marks) AS highest_mark
FROM students
GROUP BY grade;

📌 MIN() → Lowest Marks per Grade

SELECT grade, MIN(marks) AS lowest_mark
FROM students
GROUP BY grade;

📘 Summary

Clause

Works On

GROUP BY

Groups rows

COUNT()

Number of rows

SUM()

Total value

AVG()

Average value

MAX()

Highest value

MIN()

Lowest value

WHERE

Filter BEFORE grouping

HAVING

Filter AFTER grouping