SQL Aggregate Functions & Grouping (GROUP BY, HAVING) 📊
SQL Aggregate Functions & Grouping (GROUP BY, HAVING) 📊
Aggregate functions perform calculations on a set of values and return a single result.
1️⃣ Aggregate Functions
Function Description
COUNT() Returns the number of rows.
SUM() Returns the total sum of a numeric column.
AVG() Returns the average value.
MIN() Returns the smallest value.
MAX() Returns the largest value.
✅ 1. COUNT() – Counting Rows
SELECT COUNT(*) FROM students;
🔹 Output:
count
3
✅ 2. SUM() – Total of a Column
SELECT SUM(age) FROM students;
🔹 Output:
sum
65
✅ 3. AVG() – Average Value
SELECT AVG(age) FROM students;
🔹 Output:
avg
21.67
✅ 4. MIN() – Smallest Value
SELECT MIN(age) FROM students;
🔹 Output:
min
20
✅ 5. MAX() – Largest Value
SELECT MAX(age) FROM students;
🔹 Output:
max
23
2️⃣ Grouping Data with GROUP BY
The GROUP BY clause groups rows that have the same values in a column.
✅ Example: Count students per age group
SELECT age, COUNT(*) AS student_count
FROM students
GROUP BY age;
🔹 Output:
age student_count
20 1
22 1
23 1
✅ Example: Average age per department
SELECT department, AVG(age) AS avg_age
FROM students
GROUP BY department;
🔹 Output:
department avg_age
Science 21.5
Arts 22.0
3️⃣ Filtering Grouped Data with HAVING
WHERE filters rows before grouping.
HAVING filters groups after aggregation.
✅ Example: Show age groups with more than 1 student
SELECT age, COUNT(*) AS student_count
FROM students
GROUP BY age
HAVING COUNT(*) > 1;
🔹 Output:
age student_count
22 2
✅ Example: Departments with an average age above 21
SELECT department, AVG(age) AS avg_age
FROM students
GROUP BY department
HAVING AVG(age) > 21;
🔹 Output:
department avg_age
Science 22.5
🎯 Summary
✔ COUNT() – Counts rows.
✔ SUM() – Total sum of a column.
✔ AVG() – Average value.
✔ MIN() – Smallest value.
✔ MAX() – Largest value.
✔ GROUP BY – Groups rows based on a column.
✔ HAVING – Filters grouped results.
Date: 2025-03-29 00:00:00.000000