Aggregates, GROUP BY & Nulls

Lecture 4 · summarizing many rows into one value, grouping, filtering groups, and the tricky behaviour of null.

Aggregate functions

avg

average

min

minimum

max

maximum

sum

total

count

how many

SELECT avg(salary) FROM instructor WHERE dept_name = 'CSE';
SELECT count(distinct ID) FROM courseAllocation WHERE semester = 'Fall';
SELECT count(*) FROM instructor;         -- number of rows

Every example uses this instructor table:

ID	name	dept_name	salary
1	Abdur	CSE	10000
2	aman	CSE	12500
3	bilal	CSE	17000
4	morshed	EEE	12000
5	rashid	BBA	10000
6	irshad	BBA	7800

6 tuples · 4 attributes

GROUP BY — aggregate per group

Task

Average salary in each department.

sql

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

↓ Result one row per department

dept_name	avg_salary
CSE	13166
EEE	12000
BBA	8900

3 tuples · 2 attributes

The golden rule: every column in SELECT that is not inside an aggregate must appear in GROUP BY. So select dept_name, ID, avg(salary) … group by dept_name is erroneous (ID isn't grouped).

HAVING — filter the groups

Task

Departments whose average salary exceeds 10000.

sql

SELECT dept_name, avg(salary) AS avg_salary
FROM   instructor
GROUP BY dept_name
HAVING avg(salary) > 10000;

↓ Result BBA (avg 8900) is filtered out

dept_name	avg_salary
CSE	13166
EEE	12000

2 tuples · 2 attributes

VVIP (the professor's word): WHERE filters rows before groups form; HAVING filters groups after aggregation. The single most-tested distinction.

SELECT → FROM → WHERE → GROUP BY → HAVING → ORDER BY

Null values

null = unknown or “does not exist”.
Any arithmetic with null is null: 5 + null → null.
Test with IS NULL / IS NOT NULL — never = null.

SELECT name FROM instructor WHERE salary IS NULL;

Three-valued logic (true / false / unknown)

Any comparison with null returns unknown (even null = null).

unknown OR truetrue

unknown OR falseunknown

unknown AND trueunknown

unknown AND falsefalse

NOT unknownunknown

A where predicate that evaluates to unknown is treated as false — the row is dropped.

Nulls in aggregation

All aggregates except count(*) ignore null values.
On an all-null column: count returns 0; every other aggregate returns null.
count(salary) counts only non-null salaries; count(*) counts every row.

Next: Set Operations & Subqueries →