SQL GROUP BY Clause 📦¶

Mentor's Note: A simple AVG gives you the average salary for the whole company. But what if you want the average salary for each department separately? GROUP BY is the tool that puts data into "Buckets" before calculating. 💡

🌟 The Scenario: The Fruit Basket 🧺¶

Imagine a basket of fruits: Apples, Bananas, and Oranges. - Step 1 (Grouping): You separate them into three piles (The buckets). - Step 2 (Aggregate): You count how many are in each pile. - Result: "3 Apples, 5 Bananas, 2 Oranges." ✅

🎨 Visual Logic: The Bucketing Process¶

graph TD
    A[Raw Data] --> B{GROUP BY City}
    B -- "Surat" --> C[Bucket A]
    B -- "Mumbai" --> D[Bucket B]
    C --> E[AVG Salary: 50k]
    D --> F[AVG Salary: 65k]

💻 1. The Basic Syntax¶

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

Example: Salary by Department¶

-- Scenario: Find the total budget for each department
SELECT dept_name, SUM(salary) AS total_budget
FROM employees
GROUP BY dept_name;

💻 2. Grouping by Multiple Columns 🪜¶

You can create "sub-buckets".

-- Scenario: Find headcount per City within each Department
SELECT dept_name, city, COUNT(*) AS emp_count
FROM employees
GROUP BY dept_name, city;

⚠️ The Golden Rule of Grouping¶

Every column in your SELECT list that is NOT part of an aggregate function MUST be in the GROUP BY clause.

-- ❌ WRONG (Will cause an error)
SELECT name, dept_name, AVG(salary) FROM employees GROUP BY dept_name;

-- ✅ CORRECT
SELECT dept_name, AVG(salary) FROM employees GROUP BY dept_name;

💡 Interview Tip 👔¶

"Interviewers love asking about the difference between GROUP BY and ORDER BY. Answer: GROUP BY combines rows to summarize them. ORDER BY just changes the display order of the rows!"