🔥 Let’s move to the next topic in the SQL Roadmap:

✅ GROUP BY & Aggregation Functions

🧠 1. What is GROUP BY?

GROUP BY is used to group rows with same values

👉 It helps you summarize data

💡 Example Table: employees
name department salary
Amit IT 60000
Neha HR 40000
Ravi IT 70000
Sara HR 50000

👉 Without GROUP BY

SELECT AVG(salary) FROM employees;

✔ Gives overall average

👉 With GROUP BY

SELECT department, AVG(salary)
FROM employees
GROUP BY department;

✔ Gives average salary per department

⚡ 2. Aggregation Functions

These functions perform calculations on data

🔹 COUNT() → number of rows

SELECT COUNT() FROM employees;

🔹 SUM() → total

SELECT SUM(salary) FROM employees;

🔹 AVG() → average

SELECT AVG(salary) FROM employees;

🔹 MIN() → smallest value

SELECT MIN(salary) FROM employees;

🔹 MAX() → largest value

SELECT MAX(salary) FROM employees;

🎯 3. GROUP BY + Aggregation

👉 Count employees in each department

SELECT department, COUNT()
FROM employees
GROUP BY department;

👉 Total salary per department

SELECT department, SUM(salary)
FROM employees
GROUP BY department;

👉 Highest salary per department

SELECT department, MAX(salary)
FROM employees
GROUP BY department;

🚨 4. Important Rule (Interview Favorite)

👉 Every column in SELECT must be:
- Either inside GROUP BY
- Or used with aggregation function

❌ Wrong:

SELECT name, AVG(salary) FROM employees;

✅ Correct:

SELECT department, AVG(salary)
FROM employees
GROUP BY department;

🎯 5. Practice Tasks
1. Count total employees
2. Find total salary of all employees
3. Find average salary per department
4. Find maximum salary in each department
5. Count employees in each department

✅ Practice Task Solution

✅ 1. Count total employees

SELECT COUNT() FROM employees;


✅ 2. Find total salary of all employees

SELECT SUM(salary) FROM employees;


✅ 3. Find average salary per department

SELECT department, AVG(salary)
FROM employees
GROUP BY department;


✅ 4. Find maximum salary in each department

SELECT department, MAX(salary)
FROM employees
GROUP BY department;


✅ 5. Count employees in each department

SELECT department, COUNT()
FROM employees
GROUP BY department;

⚡ Mini Challenge 🔥

👉 Find department with highest average salary

⚡ Mini Challenge Solution 🔥

SELECT department, AVG(salary) AS avg_salary
FROM employees
GROUP BY department
ORDER BY avg_salary DESC
LIMIT 1;

⚡ Double Tap ❤️ For More