Grouping, Subqueries and CTEs
Before you start
You need: joins (Article 06) and functions (Article 05).
Time: about 45 minutes, plus the practice.
Learning objective
Produce a correct summary report, and choose between a subquery, a join, EXISTS and a CTE for a given problem.
Topics
GROUP BYand the grouping ruleHAVINGversusWHERE- Grouping with
CASEfor conditional counts - Scalar, list and correlated subqueries
EXISTSandNOT EXISTSINversusEXISTSand theNULLtrap- Derived tables and CTEs
- Deduplication with
ROW_NUMBER
GROUP BY
SELECT s.ClassName,
s.Section,
COUNT(*) AS StudentCount,
AVG(r.MarksObtained) AS AverageMarks,
MAX(r.MarksObtained) AS HighestMarks
FROM dbo.ExamResult AS r
JOIN dbo.Student AS s ON s.Id = r.StudentId
WHERE r.ExamId = 5 AND r.IsAbsent = 0
GROUP BY s.ClassName, s.Section
ORDER BY s.ClassName, s.Section;
The rule: every column in SELECT must either appear in GROUP BY or be inside an aggregate function.
-- Fails: Name is neither grouped nor aggregated
SELECT s.ClassName, s.Name, COUNT(*)
FROM dbo.Student AS s
GROUP BY s.ClassName;
The error is "Column 'dbo.Student.Name' is invalid in the select list because it is not contained in either an aggregate function or the GROUP BY clause." It is telling you the question is ambiguous: for a group of 40 students, which one's name did you want?
Two ways forward, depending on what you actually meant:
-- One row per class: use an aggregate
SELECT s.ClassName, MAX(s.Name) AS SampleName, COUNT(*) AS StudentCount
FROM dbo.Student AS s
GROUP BY s.ClassName;
-- One row per student, with the class total alongside: a window function
SELECT s.ClassName,
s.Name,
COUNT(*) OVER (PARTITION BY s.ClassName) AS ClassStudentCount
FROM dbo.Student AS s;
The second is usually what people wanted when they hit that error.
Grouping and NULL
GROUP BY treats all NULLs as one group — unlike =, which never matches NULL to NULL. So a GROUP BY Section over data with missing sections produces a NULL group containing all of them.
HAVING versus WHERE
SELECT s.ClassName,
COUNT(*) AS StudentCount,
AVG(r.MarksObtained) AS AverageMarks
FROM dbo.ExamResult AS r
JOIN dbo.Student AS s ON s.Id = r.StudentId
WHERE r.ExamId = 5 -- filters ROWS, before grouping
AND r.IsAbsent = 0
GROUP BY s.ClassName
HAVING AVG(r.MarksObtained) < 50 -- filters GROUPS, after aggregating
ORDER BY AverageMarks;
WHERE | HAVING | |
|---|---|---|
| Runs | Before grouping | After grouping |
| Filters | Individual rows | Whole groups |
| Can use aggregates | No | Yes |
Can use SELECT aliases | No | No (in SQL Server) |
Put every condition you can in WHERE. Filtering rows before grouping means fewer rows to aggregate. Only conditions that depend on an aggregate belong in HAVING.
-- Wasteful: aggregates every exam, then discards all but one
GROUP BY s.ClassName, r.ExamId
HAVING r.ExamId = 5
-- Correct
WHERE r.ExamId = 5
GROUP BY s.ClassName
Conditional aggregation
One pass, several counts:
SELECT s.ClassName,
COUNT(*) AS TotalResults,
SUM(CASE WHEN r.IsAbsent = 1 THEN 1 ELSE 0 END) AS AbsentCount,
SUM(CASE WHEN r.IsAbsent = 0
AND r.MarksObtained >= sub.PassingMarks
THEN 1 ELSE 0 END) AS PassCount,
SUM(CASE WHEN r.IsAbsent = 0
AND r.MarksObtained < sub.PassingMarks
THEN 1 ELSE 0 END) AS FailCount,
CAST(100.0 * SUM(CASE WHEN r.IsAbsent = 0
AND r.MarksObtained >= sub.PassingMarks
THEN 1 ELSE 0 END)
/ NULLIF(SUM(CASE WHEN r.IsAbsent = 0 THEN 1 ELSE 0 END), 0)
AS DECIMAL(5,2)) AS PassPercentage
FROM dbo.ExamResult AS r
JOIN dbo.Student AS s ON s.Id = r.StudentId
JOIN dbo.Exam AS e ON e.Id = r.ExamId
JOIN dbo.Subject AS sub ON sub.Id = e.SubjectId
WHERE r.SchoolId = 1 AND e.ExamName = 'Mid Term'
GROUP BY s.ClassName
ORDER BY PassPercentage;
Three details worth noting: absent students are excluded from the pass percentage denominator; NULLIF prevents a divide-by-zero for a class where everyone was absent; and PassingMarks comes from the subject rather than being hardcoded.
COUNT with CASE returning NULL is an alternative — COUNT skips NULLs:
COUNT(CASE WHEN r.IsAbsent = 1 THEN 1 END) AS AbsentCount
Both work. SUM(CASE ... ELSE 0 END) is more obvious to read.
ROLLUP for totals
SELECT ISNULL(s.ClassName, 'ALL CLASSES') AS ClassName,
COUNT(*) AS StudentCount
FROM dbo.Student AS s
WHERE s.SchoolId = 1 AND s.Status = 0
GROUP BY ROLLUP (s.ClassName);
ROLLUP adds a grand-total row where the grouped column is NULL — useful for report footers without a second query.
Subqueries
Scalar — returns one value
SELECT s.RollNumber,
s.Name,
(SELECT COUNT(*)
FROM dbo.ExamResult AS r
WHERE r.StudentId = s.Id) AS ResultCount
FROM dbo.Student AS s
WHERE s.SchoolId = 1;
This is correlated — the inner query references s.Id from the outer, so it runs once per outer row. Readable, and fine for small result sets; on a large table, a LEFT JOIN to a grouped subquery performs better.
A scalar subquery returning more than one row fails with "Subquery returned more than 1 value". When that error appears, the inner query is not as unique as you assumed.
List — used with IN
SELECT s.RollNumber, s.Name
FROM dbo.Student AS s
WHERE s.SchoolId = 1
AND s.Id IN (SELECT r.StudentId
FROM dbo.ExamResult AS r
WHERE r.ExamId = 5 AND r.MarksObtained >= 90);
Derived table — a subquery in FROM
SELECT s.Name,
paid.TotalPaid
FROM dbo.Student AS s
JOIN (
SELECT fa.StudentId,
SUM(p.Amount) AS TotalPaid
FROM dbo.FeeAccount AS fa
JOIN dbo.FeePayment AS p ON p.FeeAccountId = fa.Id
GROUP BY fa.StudentId
) AS paid ON paid.StudentId = s.Id
WHERE s.SchoolId = 1;
A derived table must be aliased. This is the pattern that prevents a one-to-many join from multiplying rows, as the joins article showed.
EXISTS and NOT EXISTS
-- Students who have at least one result
SELECT s.RollNumber, s.Name
FROM dbo.Student AS s
WHERE s.SchoolId = 1
AND EXISTS (SELECT 1
FROM dbo.ExamResult AS r
WHERE r.StudentId = s.Id);
-- Students with no fee account this year
SELECT s.RollNumber, s.Name
FROM dbo.Student AS s
WHERE s.SchoolId = 1
AND s.Status = 0
AND NOT EXISTS (SELECT 1
FROM dbo.FeeAccount AS fa
WHERE fa.StudentId = s.Id
AND fa.AcademicYear = '2024-25');
EXISTS stops at the first matching row rather than building a full result set. SELECT 1 inside it is conventional — the column list is never evaluated, so SELECT * is equally fast but less clear about intent.
NOT IN and the NULL trap
-- Returns NOTHING if any StudentId in ExamResult is NULL
SELECT s.Name
FROM dbo.Student AS s
WHERE s.Id NOT IN (SELECT r.StudentId FROM dbo.ExamResult AS r);
NOT IN with a NULL anywhere in the list returns no rows at all. s.Id NOT IN (1, 2, NULL) expands to s.Id <> 1 AND s.Id <> 2 AND s.Id <> NULL, and that last comparison is UNKNOWN — so the whole condition can never be TRUE.
This produces an empty report with no error, and the query looks correct. Use NOT EXISTS instead, which handles NULL correctly:
WHERE NOT EXISTS (SELECT 1 FROM dbo.ExamResult AS r WHERE r.StudentId = s.Id)
IN (without NOT) does not have this problem, but NOT EXISTS is a safe default for both.
CTEs
A common table expression is a named subquery declared before the statement that uses it.
WITH FeeSummary AS
(
SELECT fa.StudentId,
fa.TotalFees,
fa.DiscountAmount,
ISNULL(SUM(p.Amount), 0) AS TotalPaid
FROM dbo.FeeAccount AS fa
LEFT JOIN dbo.FeePayment AS p ON p.FeeAccountId = fa.Id
WHERE fa.SchoolId = 1 AND fa.AcademicYear = '2024-25'
GROUP BY fa.StudentId, fa.TotalFees, fa.DiscountAmount
)
SELECT s.RollNumber,
s.Name,
f.TotalFees,
f.TotalPaid,
f.TotalFees - f.TotalPaid - f.DiscountAmount AS Outstanding
FROM FeeSummary AS f
JOIN dbo.Student AS s ON s.Id = f.StudentId
WHERE f.TotalFees - f.TotalPaid - f.DiscountAmount > 0
ORDER BY Outstanding DESC;
A CTE is a readability tool. It is not a temporary table and it is not cached — SQL Server expands it into the main query, so referencing the same CTE twice evaluates it twice.
Several CTEs chain, each able to reference the previous ones:
WITH ActiveStudents AS
(
SELECT Id, RollNumber, Name, ClassName
FROM dbo.Student
WHERE SchoolId = 1 AND Status = 0
),
ExamAverages AS
(
SELECT r.StudentId,
AVG(r.MarksObtained) AS AverageMarks
FROM dbo.ExamResult AS r
WHERE r.IsAbsent = 0
GROUP BY r.StudentId
)
SELECT a.RollNumber, a.Name, a.ClassName, e.AverageMarks
FROM ActiveStudents AS a
JOIN ExamAverages AS e ON e.StudentId = a.Id
WHERE e.AverageMarks >= 75
ORDER BY e.AverageMarks DESC;
That reads as three named steps rather than one nested block, which is the point.
Recursive CTEs
For hierarchies of unknown depth:
WITH StaffHierarchy AS
(
-- Anchor: staff with no manager
SELECT Id, Name, ReportsToId, 0 AS Level
FROM dbo.Staff
WHERE SchoolId = 1 AND ReportsToId IS NULL
UNION ALL
-- Recursive: everyone reporting to someone already found
SELECT st.Id, st.Name, st.ReportsToId, h.Level + 1
FROM dbo.Staff AS st
JOIN StaffHierarchy AS h ON h.Id = st.ReportsToId
)
SELECT REPLICATE(' ', Level) + Name AS OrgChart, Level
FROM StaffHierarchy
ORDER BY Level, Name
OPTION (MAXRECURSION 100);
MAXRECURSION caps the depth. The default is 100; 0 means unlimited, which will hang the query if the data contains a cycle. Set an explicit limit.
Deduplication with ROW_NUMBER
A frequent real task: the same result recorded twice for one student.
-- Find them
WITH Numbered AS
(
SELECT r.Id,
r.StudentId,
r.ExamId,
ROW_NUMBER() OVER (PARTITION BY r.StudentId, r.ExamId
ORDER BY r.Id) AS RowNum
FROM dbo.ExamResult AS r
WHERE r.SchoolId = 1
)
SELECT * FROM Numbered WHERE RowNum > 1;
-- Then, after checking the list, delete them
WITH Numbered AS
(
SELECT r.Id,
ROW_NUMBER() OVER (PARTITION BY r.StudentId, r.ExamId
ORDER BY r.Id) AS RowNum
FROM dbo.ExamResult AS r
WHERE r.SchoolId = 1
)
DELETE FROM Numbered WHERE RowNum > 1;
ROW_NUMBER restarts at 1 for each (StudentId, ExamId) pair, so anything above 1 is a duplicate. The ORDER BY decides which copy survives — r.Id keeps the earliest.
Always run the SELECT version first and read the rows. Deleting through a CTE is a real DELETE, and it is not reversible outside a transaction.
Once cleaned, add the constraint that prevents it recurring:
ALTER TABLE dbo.ExamResult
ADD CONSTRAINT UQ_ExamResult UNIQUE (ExamId, StudentId);
Errors you will hit
| Message | Cause | Fix |
|---|---|---|
Column 'X' is invalid in the select list because it is not contained in either an aggregate function or the GROUP BY clause | Selected a column you did not group by | Add it to GROUP BY, or aggregate it |
Subquery returned more than 1 value | A scalar subquery matched several rows | Use IN, or add a filter |
WHERE on an aggregate does not work | Aggregates are filtered by HAVING | Move it to HAVING |
A NOT IN subquery returns nothing | The subquery contains a NULL | Use NOT EXISTS |
| Grouped counts look wrong | Grouping after a row-multiplying join | Check the join first |
NOT IN with a NULL in the list returns no rows at all — not an error, just an empty result, and the reason is rarely obvious.
Common mistakes
- A
SELECTcolumn neither grouped nor aggregated - Filtering in
HAVINGwhat belonged inWHERE NOT INagainst a subquery that can returnNULL, giving an empty result- Forgetting to alias a derived table
- Expecting a CTE to be cached, then referencing it twice
- A correlated scalar subquery over a large table where a join would do
- No
MAXRECURSIONon a recursive CTE - Running the
DELETEform of a dedupe before checking theSELECT - Dividing in an aggregate without
NULLIF - Deduplicating and not adding the constraint that stops it happening again
Practice
Write and run these — the course exercises are group reports and practical subqueries:
- Class-wise and section-wise average, highest and lowest marks for one exam, excluding absentees.
- The same with a
HAVINGclause keeping only classes averaging below 50. - Move
r.ExamId = 5fromWHEREtoHAVING. Confirm the result is the same and explain why it is still the wrong place for it. - Class-wise pass percentage in one pass with conditional aggregation, using
PassingMarksfromSubjectandNULLIFfor safety. - Student list with total fees, total paid and outstanding, using a CTE.
- Students with no fee account for 2024-25, written three ways:
LEFT JOIN ... IS NULL,NOT IN, andNOT EXISTS. - Make one
StudentIdinExamResultnullable and set one toNULL. Re-run theNOT INversion. Record the result and explain it. - Every student with their class rank, using a window function alongside their own row.
- Insert a duplicate
ExamResultdeliberately, find it withROW_NUMBER, delete it, then add the unique constraint. - A staff org chart with a recursive CTE, with
MAXRECURSIONset.
Exercise 7 is the one to remember — it is a bug that returns an empty report with no error message.
Then run the AI drill — review an AI-written query for correctness and duplication. Ask an assistant for "total fees collected per class", then check it specifically for: a one-to-many join multiplying rows, absentees or cancelled payments included wrongly, a missing SchoolId filter, and NOT IN where NOT EXISTS was needed. Record what it got wrong.
You can now
- Build a summary report with
GROUP BY - Filter groups with
HAVINGand rows withWHERE - Choose between a subquery, a join and
EXISTS - Say why
NOT INbreaks when the list containsNULL - Recognise when a join has corrupted a grouped count
Review questions
- Why must every non-aggregated
SELECTcolumn appear inGROUP BY? - When does a condition belong in
HAVINGrather thanWHERE? - Why can
NOT INwith a subquery return no rows at all? - Is a CTE cached when referenced twice in the same query?