Oracle GROUP BY & HAVING 📦

Mentor's Note: Aggregation gives you one answer for the whole table. GROUP BY gives you an answer for every group. It's like sorting your laundry into piles before counting them! 💡

---

🌟 The Scenario: The Departmental Audit 🏢

• Goal: Find the average salary for each department.
• Problem: A simple AVG(salary) gives you the average for the entire company.
• Action: Use GROUP BY department_id.

---

💻 1. The GROUP BY Clause

Combines rows with the same values into summary rows.

SELECT department_id, AVG(salary) AS "Average_Pay"
FROM employees
GROUP BY department_id;

---

💻 2. The HAVING Clause (The Group Filter)

Used to filter groups after the calculation is done. You cannot use WHERE for this!

-- Scenario: Only show departments where the total payroll is > ₹50,000
SELECT department_id, SUM(salary)
FROM employees
GROUP BY department_id
HAVING SUM(salary) > 50000;

---

🏗️ Architect's Note: Grouping Efficiency 🛡️

When Oracle groups data, it often uses a HASH GROUP BY or SORT GROUP BY operation.

• The Architect's Secret: WHERE filters rows before they are grouped. HAVING filters them after.
• Tip: Always use WHERE to remove as many rows as possible before they hit the grouping engine. This saves significant CPU and RAM!

---

📈 Learning Path

• ← Back: Joins Recap
• Next: Advanced Grouping (ROLLUP/CUBE) →