📊

Advanced SQL — Aggregates, GROUP BY, HAVING & Subqueries

Five questions that break beginners, one tiny dataset with real NULL traps. Master aggregation, the clause pipeline, and every kind of nested subquery on data you can verify by hand.

Aggregates & NULLs GROUP BY / HAVING Subqueries The NOT IN Trap

Press Next → or use ← → arrow keys

Section 01

Five Questions That Break Beginners

🎯 THE CHALLENGE

Each question exists to justify one SQL construct

The whole tutorial is five questions that get progressively harder — and each one fails unless you reach for the right tool. That's the point: you feel why the construct exists.

🧗 THE FIVE QUESTIONS

Q1

Count total employees → a single aggregate

Q2

Salary spend per department → GROUP BY

Q3

Departments averaging above 60k → HAVING

Q4

Employees above the company average → a subquery

Q5

Departments with zero staff → the NULL trap in NOT IN

Section 02

The Dataset — Small, With Traps Planted

DEPARTMENTS

DeptID	DeptName
D1	Sales
D2	Engineering
D3	HR
D4	Marketing ← 0 staff

EMPLOYEES

Emp	Dept	Salary	Comm.
Aarav	D1	50000	5000
Diya	D1	70000	NULL
Kabir	D2	80000	8000
Mira	D2	90000	NULL
Rohan	D2	60000	2000
Sara	D3	45000	NULL
Veer	NULL	55000	1000

🕳️

Three Traps on Purpose

Veer has a NULL DeptID · Marketing (D4) has zero employees · three commissions are NULL. These surface the real aggregate and subquery bugs.

Section 03

Aggregate Functions & the NULL Rule

Function	Purpose	NULL behavior
*COUNT()**	Row count	Counts rows, incl. NULLs
COUNT(col)	Value count	Counts non-NULL values only
SUM(col)	Total	Ignores NULLs
AVG(col)	Average	Sum ÷ non-NULL count
MIN / MAX	Extremes	Ignore NULLs

SELECT COUNT(*)          AS total_rows,      -- 7
       COUNT(Commission) AS with_commission, -- 4  (NULLs excluded)
       SUM(Salary)       AS payroll,         -- 450000
       AVG(Salary)       AS avg_salary,      -- 64285.71
       AVG(Commission)   AS avg_comm         -- 4000  (16000/4, not /7)
FROM   EMPLOYEES;

⚠️

The One Rule to Remember

Every aggregate ignores NULLs — except COUNT(*), which counts rows. So AVG(Commission) is 16000 ÷ 4 = 4000, not ÷ 7. Want NULLs as zero? AVG(COALESCE(Commission,0)) ≈ 2285.71.

Section 04

GROUP BY — One Summary Per Group

🧺

NULL Forms Its Own Bucket · The Single-Value Rule

Veer's NULL dept doesn't vanish — it collapses into one NULL group. And every SELECT column must be inside an aggregate or in GROUP BY — because each group emits exactly one row.

Section 05

WHERE vs HAVING — The Execution Pipeline

-- ❌ aggregates don't exist yet in WHERE
SELECT DeptID FROM EMPLOYEES
WHERE AVG(Salary) > 60000
GROUP BY DeptID;   -- error

-- ✅ filter groups with HAVING
SELECT DeptID, AVG(Salary)
FROM EMPLOYEES
GROUP BY DeptID
HAVING AVG(Salary) > 60000;  -- D2

⚡

Filter Early

WHERE filters rows; HAVING filters groups. If a condition needs no aggregate, put it in WHERE — earlier filtering is both correct and faster. (D1 averages exactly 60000, so > 60000 excludes it.)

Section 06 · Practice A

GROUP BY + JOIN, and WHERE + HAVING

P2: per department name, headcount & avg salary, highest first.

SELECT d.DeptName,
       COUNT(*) AS headcount,
       AVG(e.Salary) AS avg_salary
FROM EMPLOYEES e
JOIN DEPARTMENTS d ON d.DeptID=e.DeptID
GROUP BY d.DeptName
ORDER BY avg_salary DESC;

Result

Engineering 3 76666.67 Sales 2 60000 HR 1 45000

P3: among earners ≥ 50k, depts with > 1 such employee.

SELECT DeptID, COUNT(*) AS big_earners
FROM EMPLOYEES
WHERE Salary >= 50000
GROUP BY DeptID
HAVING COUNT(*) > 1;

Result

D1 2 D2 3

WHERE drops Sara (45k) before grouping; D3 then has only 1 row.

🔗

The INNER JOIN Quietly Filters

Veer (NULL dept) drops out of P2, and Marketing never appears — it has no employees to join to.

Section 07

Subqueries — When One Query Isn't Enough

"Who earns more than the company average?" You can't write WHERE Salary > AVG(Salary) — so nest a query that returns the average first.

SELECT Name, Salary
FROM EMPLOYEES
WHERE Salary > (SELECT AVG(Salary) FROM EMPLOYEES);   -- scalar subquery

🎯

A Subquery Is Just a SELECT Inside a SELECT

The inner query produces a value (or list, or table); the outer query uses it. Non-correlated subqueries like this run once.

Section 07 · Placement

Three Places a Subquery Can Live

🔎

In WHERE / HAVING

scalar or IN-list

A single value for a comparison, or a list for IN.
WHERE DeptID IN (SELECT DeptID FROM EMPLOYEES)

🧱

In FROM

derived table · needs alias

Treat a query's result as a temporary table — enables aggregate of an aggregate. Must be named: ) AS t.

📎

In SELECT

scalar per row

Attach a computed value to every output row — e.g. the company average beside each employee.

-- FROM: aggregate of an aggregate (average department payroll)
SELECT AVG(dept_total) AS avg_dept_payroll
FROM ( SELECT DeptID, SUM(Salary) AS dept_total
        FROM EMPLOYEES WHERE DeptID IS NOT NULL
        GROUP BY DeptID ) AS t;

Section 08

Correlated Subqueries & EXISTS

A correlated subquery references the outer row — conceptually re-running for each candidate. "Does this employee beat their own department's average?"

SELECT e.Name, e.Salary, e.DeptID
FROM EMPLOYEES e
WHERE e.Salary > (SELECT AVG(e2.Salary) FROM EMPLOYEES e2
                   WHERE e2.DeptID = e.DeptID);   -- references outer e

Result

Diya 70000 D1 (> 60000 ✓) Kabir 80000 D2 (> 76666 ✓) Mira 90000 D2 (> 76666 ✓)

✅

EXISTS — the NULL-safe check

EXISTS tests presence (true/false), stops at the first match, and is NULL-safe — unlike IN/NOT IN.

Section 09 · Practice B

Subquery in HAVING & Multi-Level Aggregation

P4: departments beating the company average.

SELECT DeptID, AVG(Salary) AS dept_avg
FROM EMPLOYEES
GROUP BY DeptID
HAVING AVG(Salary) >
  (SELECT AVG(Salary) FROM EMPLOYEES);

Result

D2 76666.67 (company avg 64285.71)

P6: average of each department's payroll total.

SELECT AVG(dept_total)
FROM ( SELECT DeptID, SUM(Salary) dept_total
        FROM EMPLOYEES
        WHERE DeptID IS NOT NULL
        GROUP BY DeptID ) AS t;

Result

(120000+230000+45000)/3 = 131666.67

🧮

One Level = One Aggregation Round

You can't nest GROUP BY inside GROUP BY. Comparing or re-aggregating an aggregate needs a second level — a subquery in HAVING or FROM.

Section 09 · The Trap

The NOT IN NULL Trap

P7: "Find the department with no employees." The obvious query returns zero rows — silently.

-- ❌ silently returns nothing
SELECT DeptName FROM DEPARTMENTS
WHERE DeptID NOT IN
  (SELECT DeptID FROM EMPLOYEES);

-- ✅ NULL-safe anti-join
SELECT d.DeptName FROM DEPARTMENTS d
WHERE NOT EXISTS
  (SELECT 1 FROM EMPLOYEES e
   WHERE e.DeptID = d.DeptID);

🧨

Three-Valued Logic Bites

A NULL in the inner list makes NOT IN evaluate to UNKNOWN — never TRUE — so no rows survive. Use NOT EXISTS for anti-joins.

Section 09 · P8

Scalar Subquery in SELECT

Show each employee's distance from the company average — attach the same scalar to every row.

SELECT Name, Salary,
       Salary - (SELECT AVG(Salary) FROM EMPLOYEES) AS diff
FROM EMPLOYEES;   -- company avg = 64285.71

Name	Salary	diff
Aarav	50000	-14285.71
Diya	70000	+5714.29
Kabir	80000	+15714.29
Mira	90000	+25714.29
Rohan	60000	-4285.71
Sara	45000	-19285.71
Veer	55000	-9285.71

Section 10

Six Common Mistakes

🏷️

Ungrouped column

SELECT Name, SUM(Salary) … GROUP BY DeptID — which name for a 3-person group? Strict SQL rejects it.

🚫

Aggregate in WHERE

WHERE runs before groups exist. Use HAVING for aggregate conditions.

🧨

NOT IN meets NULL

A NULL in the inner list → UNKNOWN → empty result. Prefer NOT EXISTS.

🔢

COUNT(*) vs COUNT(col)

COUNT(*)=7 counts rows; COUNT(Commission)=4 skips NULLs. Know which you need.

🏷️

Missing derived alias

A subquery in FROM must be named: FROM (…) AS t.

💥

Scalar returns many rows

A = (subquery) that yields multiple rows crashes. Use IN, or ensure one row.

Section 12

Golden Rules of Advanced SQL

🏆 NON-NEGOTIABLE RULES

1

Replay the pipeline: FROM → WHERE → GROUP BY → HAVING → SELECT → ORDER BY. Most errors violate this order.

2

WHERE filters rows; HAVING filters groups. Non-aggregate conditions belong in WHERE — earlier & cheaper.

3

Every SELECT column is aggregated or grouped — one output row per group means one value per expression.

4

Respect NULLs: aggregates skip them, COUNT(*) doesn't, GROUP BY makes a NULL bucket, and comparisons with NULL are UNKNOWN.

5

Use NOT EXISTS for anti-joins — NOT IN with a nullable inner column fails silently.

6

One query level = one aggregation round. Re-aggregating needs a subquery in HAVING or FROM.

7

Verify on paper-sized data first — hand-compute on 7 rows before trusting 7 million.

FINAL

From Rows to Insight

5Aggregate functions

6Pipeline stages

3Subquery locations

3VLTRUE / FALSE / UNKNOWN

🎯

The Foundation Is Set

You can now summarise with aggregates, bucket with GROUP BY, filter groups with HAVING, and reach for the right subquery — scalar, IN, derived table, or correlated — while sidestepping the NULL traps. Next up: CTEs (WITH) and window functions, which make row-to-group comparisons effortless.

🧠

One Sentence to Remember

Aggregates skip NULLs, the clause pipeline explains everything, and when you must compare a row to an aggregate — nest a subquery, and prefer NOT EXISTS over NOT IN.

📊 End of tutorial · Press ← to review, or click Restart