06 - SQL Aggregation & Grouping

🎯 Learning Objectives

• Use aggregate functions (COUNT, SUM, AVG, MIN, MAX) correctly
• Write GROUP BY clauses to group rows for aggregation
• Filter groups with the HAVING clause
• Understand NULL behavior in aggregations
• Explain 3-valued logic (TRUE, FALSE, UNKNOWN)

📋 Prerequisites

• 05 - SQL Queries — SELECT, WHERE, ORDER BY
• Understanding of NULL values

📖 Core Content

6.1 Intuition: From Individual Rows to Summaries

• What's the average salary in each department?
• How many students are in each course?
• What's the total budget across all departments? Aggregation answers these questions. Instead of returning one row per data item, it returns one row per group.

Why This Matters: In real-world analytics, you rarely look at raw data — you look at aggregated summaries. Dashboards, reports, and business intelligence all rely on aggregation.

6.2 Aggregate Functions

Basic Examples

sql
-- Total number of instructors
SELECT COUNT(*) FROM instructor;
-- Result: 12
-- Number of instructors who have a non-NULL salary
SELECT COUNT(salary) FROM instructor;
-- 12 (if all have salaries)
-- Number of distinct departments
SELECT COUNT(DISTINCT dept_name) FROM instructor;
-- Result: 7
-- Average salary
SELECT AVG(salary) FROM instructor;
-- Result: ~75500
-- Maximum salary
SELECT MAX(salary) FROM instructor;
-- Result: 95000

6.3 GROUP BY: Grouping Rows

sql
SELECT dept_name, AVG(salary) AS avg_salary
FROM instructor
GROUP BY dept_name;

Critical Rule: SELECT columns must be in GROUP BY or aggregated

sql
-- ERROR: name is neither in GROUP BY nor aggregated
SELECT dept_name, name, AVG(salary)
FROM instructor
GROUP BY dept_name;

sql
-- CORRECT
SELECT dept_name, AVG(salary)
FROM instructor
GROUP BY dept_name;

Exception: MySQL/SQLite allow this (returning the first value encountered), but PostgreSQL and standard SQL reject it. Write portable SQL by following the rule.

6.4 HAVING: Filtering Groups

sql
SELECT dept_name, AVG(salary) AS avg_salary
FROM instructor
GROUP BY dept_name
HAVING AVG(salary) > 70000;

WHERE vs. HAVING: When to Use Which

sql
-- WHERE filters rows BEFORE grouping
SELECT dept_name, AVG(salary)
FROM instructor
WHERE salary > 50000  -- excludes low salaries before averaging
GROUP BY dept_name;
-- HAVING filters groups AFTER grouping
SELECT dept_name, AVG(salary)
FROM instructor
GROUP BY dept_name
HAVING AVG(salary) > 50000;  -- excludes depts with low avg salary

• Filter on individual rows → WHERE
• Filter on aggregate results → HAVING
• You can use both in the same query

6.5 NULLs and 3-Valued Logic

Truth Tables

NULL in Aggregations

sql
-- All aggregate functions except COUNT(*) ignore NULLs
SELECT AVG(salary) FROM instructor;  -- average of only non-NULL salaries
SELECT COUNT(*) FROM instructor;      -- counts ALL rows
SELECT COUNT(salary) FROM instructor; -- counts only rows with non-NULL salary

• COUNT(*) → number of rows (not 0!)
• COUNT(column) → 0
• SUM(column) → NULL
• AVG(column) → NULL
• MIN(column) → NULL
• MAX(column) → NULL

6.6 Worked Examples

Example 1: Department salary statistics

sql
SELECT dept_name,
       COUNT(*) AS num_instructors,
       AVG(salary) AS avg_salary,
       MIN(salary) AS min_salary,
       MAX(salary) AS max_salary
FROM instructor
GROUP BY dept_name;

Example 2: Departments with at least 2 instructors

sql
SELECT dept_name, COUNT(*) AS count
FROM instructor
GROUP BY dept_name
HAVING COUNT(*) >= 2;

Example 3: WHERE + GROUP BY + HAVING together

sql
SELECT dept_name, AVG(salary) AS avg_high_salary
FROM instructor
WHERE salary > 60000       -- 1. Only consider high-salary instructors
GROUP BY dept_name          -- 2. Group by department
HAVING AVG(salary) > 80000; -- 3. Only departments with avg > 80000

📐 Key Formulas / Concepts

⚠️ Common Pitfalls

Pitfall 1: Using column not in GROUP BY in SELECT

Pitfall 2: Using HAVING without GROUP BY

Pitfall 3: Confusing WHERE and HAVING

sql
SELECT dept_name, AVG(salary) FROM instructor WHERE AVG(salary) > 70000 GROUP BY dept_name;

sql
SELECT dept_name, AVG(salary) FROM instructor GROUP BY dept_name HAVING AVG(salary) > 70000;

📝 Practice Questions

Q1. Write a query to find the number of instructors in each department.

Answer

sql
SELECT dept_name, COUNT(*) AS num_instructors
FROM instructor
GROUP BY dept_name;

Q2. Find the average salary of instructors in departments with at least 2 instructors.

Answer

sql
SELECT dept_name, AVG(salary) AS avg_salary
FROM instructor
GROUP BY dept_name
HAVING COUNT(*) >= 2;

Q3. What is the difference between COUNT(*) and COUNT(column)?

Answer

• COUNT(*) counts all rows in the group (including rows with NULL values)
• COUNT(column) counts only non-NULL values in that column

If a group has 10 rows but column salary has 2 NULLs, COUNT(*) = 10 but COUNT(salary) = 8.

Q4. What does SUM(salary) return if all salaries are NULL?

Answer

SUM(salary) returns NULL, not 0. All aggregate functions except COUNT(*) return NULL when operating on all-NULL values.

COUNT(*) would return the number of rows. COUNT(salary) would return 0.

Q5. Write a query to find departments whose total budget (sum of instructor salaries) exceeds 200000.

Answer

sql
SELECT dept_name, SUM(salary) AS total_salary
FROM instructor
GROUP BY dept_name
HAVING SUM(salary) > 200000;

Q6. Explain the difference between WHERE and HAVING in a GROUP BY query.

Answer

• WHERE: Filters rows before grouping. Conditions can reference individual columns but NOT aggregate functions.
• HAVING: Filters groups after aggregation. Conditions CAN reference aggregate functions.

Execution order: FROM → WHERE → GROUP BY → HAVING → SELECT

Q7. How does NULL behave in AND/OR/NOT operations?

Answer

• NULL AND TRUE = UNKNOWN
• NULL AND FALSE = FALSE (short-circuit!)
• NULL OR TRUE = TRUE (short-circuit!)
• NULL OR FALSE = UNKNOWN
• NOT NULL = UNKNOWN

WHERE clause treats UNKNOWN as FALSE, so the row is not included.

Q8. Find the minimum and maximum salary for each department, ordered by department name.

Answer

sql
SELECT dept_name, MIN(salary) AS min_salary, MAX(salary) AS max_salary
FROM instructor
GROUP BY dept_name
ORDER BY dept_name;

🔗 Cross-References

• Next Topic: 07 - Advanced SQL
• Previous Topic: 05 - SQL Queries
• Related: BSMS2001 (BDM) — Business reporting, analytics queries
• Textbook: Silberschatz, Korth, Sudarshan — Chapter 3 (SQL), Chapter 4 (Intermediate SQL) Join Discord Previous05 - SQL QueriesNext07 - Advanced SQL