Skip to content

SQL Grouping (GROUP BY) 🚀

Prerequisites: SQL Filtering (WHERE)

Mentor's Note: Sometimes you don't want to see individual rows; you want the "Big Picture." Grouping is how we summarize thousands of rows into a single meaningful number! 💡

🌟 The Scenario: The Class Report Card 📝

Imagine you are a teacher with 100 students in 4 different departments (IT, CS, Math, Bio).

  • The Logic: You don't want to see every student's individual marks. You want to see the Average Mark for EACH department. 📦
  • The Result: You group the students by their "Department" label and calculate the mean. This is exactly what GROUP BY does. ✅

📖 Concept Explanation

1. Aggregate Functions (The Calculators)

  • COUNT(): How many items? 🔢
  • SUM(): Total sum. ➕
  • AVG(): Average value. 📊
  • MAX() / MIN(): Highest and Lowest.

2. The GROUP BY Clause

This clause groups rows that have the same values into summary rows.

3. The HAVING Clause

A common mistake is using WHERE to filter groups. - WHERE: Filters individual rows BEFORE grouping. - HAVING: Filters the summary groups AFTER they are calculated.

🎨 Visual Logic: The Grouping Process

graph TD
    A[Raw Data: 100 Students] --> B{GROUP BY Dept}
    B --> C[IT Group]
    B --> D[CS Group]
    C -- AVG --> E[IT Average: 85]
    D -- AVG --> F[CS Average: 90]
    E --> G[Final Report ✅]
    F --> G

💻 Implementations (Dialect Comparison)

-- 🚀 Action: Total salary budget per department
SELECT dept_id, SUM(salary)
FROM employees
GROUP BY dept_id
HAVING SUM(salary) > 50000;

-- 🚀 Action: Same logic as Oracle
SELECT dept_id, SUM(salary)
FROM employees
GROUP BY dept_id
HAVING SUM(salary) > 50000;

-- 🚀 Action: Same logic as Oracle
SELECT dept_id, SUM(salary)
FROM employees
GROUP BY dept_id
HAVING SUM(salary) > 50000;

📊 Sample Dry Run (HAVING)

Logic: GROUP BY City HAVING COUNT(*) > 1

City Rows Count HAVING (>1)? Result
Surat 5 5 ✅ Yes SHOW
Mumbai 1 1 ❌ No HIDE

📉 Technical Analysis

  • The "Rule of One": Any column in your SELECT list that is not part of an aggregate function MUST appear in the GROUP BY clause. If you forget this, you will get an error! ⚠️

🎯 Practice Lab 🧪

Task: The Genre Count

Task: You have a BOOKS table with a genre column. Write a query to show how many books are there in each genre. Hint: SELECT genre, COUNT(*) FROM books GROUP BY genre; 💡

💡 Interview Tip 👔

"Interviewers ALWAYS ask: 'Can we use aggregate functions in a WHERE clause?' Answer: NO. Aggregate functions work on groups, so they can only be used in the SELECT list or the HAVING clause!"

💡 Pro Tip: "The whole is greater than the sum of its parts." - Aristotle

← Back: Joins | Next: Subqueries →