Aggregates, GROUP BY, HAVING; COUNT(*) vs COUNT(col); NULL handling

0 · Recall Warm-up

Answer from memory first. Answers are in the Answer Vault at the bottom.

1. What is TRUE AND UNKNOWN, and FALSE AND UNKNOWN? from Lesson 3.3.4
2. Does SQL keep duplicate rows in results? from Lesson 3.3.3
3. What does γ do in extended RA? from Lesson 3.1.6

1 · The Hook

"How many students are there?" "Average CGPA per department?" "Departments with more than one student?" These need aggregates. Two NULL rules create the classic GATE question: COUNT(*) and COUNT(cgpa) give different answers, and AVG(cgpa) is not SUM(cgpa) / COUNT(*).

2 · Predict First

With Esha's NULL CGPA, what are COUNT(*), COUNT(cgpa) and AVG(cgpa) over STUDENT?

Make a guess in your head. The answer opens Section 3.

3 · Core Concept

Predict First — the answer

COUNT(*) = 5 (rows). COUNT(cgpa) = 4 (non-NULL values). AVG(cgpa) = (9.1 + 7.8 + 8.4 + 6.9) / 4 = 32.2 / 4 = 8.05: NULL is ignored, so it divides by 4, not 5.

Aggregate functions

So AVG(col) = SUM(col) / COUNT(col), not SUM(col) / COUNT(*). SUM(DISTINCT col) and AVG(DISTINCT col) use each value once.

Empty input: COUNT returns 0; SUM, AVG, MIN, MAX return NULL.

GROUP BY

GROUP BY dept splits the rows into groups with equal dept; each group produces one output row.

SELECT dept, COUNT(*) AS n, AVG(cgpa) AS avg_cgpa
FROM STUDENT
GROUP BY dept;

CS has 3 rows but AVG uses Asha and Chirag only: (9.1 + 8.4) / 2 = 8.75.

SELECT-list rule: with GROUP BY, the SELECT list may contain only grouping columns and aggregates. SELECT name, dept, COUNT(*) … GROUP BY dept is an error: which name would a group of three show?

NULLs in a grouping column form one group of their own.

HAVING

HAVING filters groups after grouping (WHERE filters rows before):

SELECT dept, COUNT(*) FROM STUDENT
WHERE cgpa > 7          -- rows: Asha, Bhavin, Chirag
GROUP BY dept
HAVING COUNT(*) >= 2;   -- groups: CS (2)

Result: (CS, 2). Esha is removed by WHERE (UNKNOWN), Diya too (6.9).

Full evaluation order

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

• Aggregates cannot appear in WHERE (groups do not exist yet): WHERE COUNT(*) > 1 is an error.
• HAVING without GROUP BY treats the whole table as one group.
• An aggregate with no GROUP BY returns exactly one row.

4 · Visual

FROM STUDENT -> WHERE cgpa > 7 -> GROUP BY dept -> HAVING COUNT(*) >= 2 -> SELECT

 5 rows         3 rows             CS: Asha, Chirag    CS (2) kept          (CS, 2)
                (Esha: UNKNOWN,    EE: Bhavin          EE (1) dropped
                 Diya: 6.9 fail)

5 · Worked Examples

Example 1 Easy

Compute over ENROLL: COUNT(*), COUNT(DISTINCT roll), COUNT(DISTINCT cid).

Solution

7, 4, 4. (Esha has no enrolments.)

Example 2 Medium

SELECT cid, COUNT(*) FROM ENROLL GROUP BY cid HAVING COUNT(*) > 1;

Solution

C1: 2, C2: 2, C3: 2, C4: 1. Result: (C1, 2), (C2, 2), (C3, 2).

Example 3 GATE

What does SELECT SUM(cgpa) / COUNT(*), AVG(cgpa) FROM STUDENT; return?

Solution

SUM = 32.2. 32.2 / 5 = 6.44 (treats Esha as 0), while AVG = 32.2 / 4 = 8.05. The two differ exactly because of the NULL.

8 · Connect the Dots

Lesson 3.3.4 finished NULL logic; this lesson added aggregates and grouping. Next: SQL's explicit JOIN syntax, including the outer joins of Lesson 3.1.4.

9 · Self-Test

GATE format. Try every question before opening the vault.

Q1 · NAT · 1 mark(s) — COUNT

On STUDENT (5 rows, Esha's cgpa NULL), what is COUNT(cgpa)?

Numerical answer: ________

Q2 · NAT · 2 mark(s) — AVG

What is AVG(cgpa) for dept = 'CS' rows? (Asha 9.1, Chirag 8.4, Esha NULL)

Numerical answer: ________

Q3 · NAT · 2 mark(s) — GROUP BY / HAVING

How many rows: SELECT dept FROM STUDENT GROUP BY dept HAVING COUNT(cgpa) = 1;

Numerical answer: ________

Q4 · MCQ · 1 mark(s) — Errors

Which query is invalid?

1. (A) SELECT dept, COUNT(*) FROM STUDENT GROUP BY dept;
2. (B) SELECT dept FROM STUDENT GROUP BY dept HAVING AVG(cgpa) > 7;
3. (C) SELECT COUNT(*) FROM STUDENT;
4. (D) SELECT dept, name FROM STUDENT GROUP BY dept;

Q5 · MCQ · 2 mark(s) — Empty input

SELECT COUNT(*), MAX(cgpa) FROM STUDENT WHERE dept = 'CE'; (no CE students) returns:

1. (A) one row (NULL, NULL)
2. (B) no rows
3. (C) one row (0, 0)
4. (D) one row (0, NULL)

11 · Concept Ledger

Unlocked in this lesson. From here on, these terms may be used.

aggregate functionCOUNT(*) vs COUNT(col)COUNT(DISTINCT)SUMAVGMINMAXGROUP BYHAVINGWHERE vs HAVINGfull evaluation order