Skip to content

Oracle GROUP BY & HAVING 📦

Prerequisites: Aggregates Overview

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