Lecture 07: Aggregation & Grouping

Lecture Source Slide Deck

Original Slides: Lecture 07 (38 slides) · Amit Kumar Dhar (IIT Bhilai)
Download lecture07.pdf

1. Aggregate Functions

Aggregate functions take a collection of values across multiple rows and condense them into a single scalar summary value:

Function	Description	`NULL` Handling
`COUNT(*)`	Counts total number of rows returned.	Includes rows with NULLs.
`COUNT(column)`	Counts rows where `column` is not null.	Ignores NULLs.
`COUNT(DISTINCT col)`	Counts distinct non-null values.	Ignores NULLs.
`SUM(column)`	Calculates total sum of numeric values.	Ignores NULLs (returns `NULL` if all rows are null).
`AVG(column)`	Arithmetic mean (`sumcount of non-nulls`).	Ignores NULLs.
`MIN(column)` / `MAX(column)`	Smallest / largest value.	Ignores NULLs.

The Golden Rule of NULLs in Aggregation

AVG(salary) divides the sum only by rows where salary IS NOT NULL. It does not treat NULL as zero. If you want NULL treated as zero, you must write AVG(COALESCE(salary, 0)).

2. `GROUP BY`: Partitioning Rows into Buckets

The GROUP BY clause divides table rows into distinct subsets sharing common attribute values, evaluating aggregate functions independently for each subset.

SELECT 
    dept_name,
    COUNT(*) AS total_faculty,
    ROUND(AVG(salary), 2) AS average_salary,
    MAX(salary) AS highest_salary
FROM instructor
GROUP BY dept_name;

The Cardinal Rule of `GROUP BY`

Any column appearing in the SELECT list must either be specified directly in the GROUP BY clause, or be enclosed inside an aggregate function.

-- WRONG in ANSI SQL: 'name' is not in GROUP BY and not aggregated!
SELECT dept_name, name, AVG(salary)
FROM instructor
GROUP BY dept_name;

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

3. `HAVING` vs. `WHERE`

A common mistake is attempting to filter aggregated values inside a WHERE clause.

Execution Pipeline:
1. FROM               Identify tables & joins
2. WHERE              Filter individual rows BEFORE grouping
3. GROUP BY           Group rows into buckets
4. HAVING             Filter grouped buckets based on aggregate results
5. SELECT             Project columns & evaluate aggregates
6. ORDER BY           Sort final result rows

-- Find departments with more than 2 instructors whose average salary exceeds 70,000
SELECT 
    dept_name,
    COUNT(*) AS num_instructors,
    AVG(salary) AS avg_sal
FROM instructor
WHERE salary > 50000        -- Filter individual instructors FIRST
GROUP BY dept_name          -- Group by department
HAVING COUNT(*) > 2         -- Filter groups AFTER aggregation
   AND AVG(salary) > 70000;

4. Topic Practice Questions

SELECT 
    d.dept_name, 
    COUNT(s.id) AS student_count
FROM department d
LEFT JOIN student s ON d.dept_name = s.dept_name
GROUP BY d.dept_name;

Note: Using COUNT(s.id) is critical here! COUNT(*) would incorrectly return 1 for departments with 0 students due to the left join's null-padded row.

Lecture 07: Aggregation & Grouping

1. Aggregate Functions

2. GROUP BY: Partitioning Rows into Buckets

The Cardinal Rule of GROUP BY

3. HAVING vs. WHERE

4. Topic Practice Questions

Q1: Given an employees table with (id, salary, bonus) where bonus can be NULL. What is the difference between AVG(bonus) and AVG(COALESCE(bonus, 0))?

Q2: Write a query to find all departments and the number of students in each, showing 0 for departments with no students.

Q3: Find all course titles that have been offered in at least two distinct semesters.

On this page

2. `GROUP BY`: Partitioning Rows into Buckets

The Cardinal Rule of `GROUP BY`

3. `HAVING` vs. `WHERE`