GROUP BY, HAVING and Aggregate Functions – SQL Server | Lesson 18

Published / updated4 Oct 2026

Introduction

The principal does not want 600,000 attendance rows. She wants one number: today's attendance percentage. The accountant wants another: fees collected per class.

Turning many rows into a few numbers is called aggregation. In this lesson, you learn the five aggregate functions, GROUP BY to get one number per group, and HAVING to filter those groups.

By the end of this lesson, you will be able to:

Use COUNT, SUM, AVG, MIN, and MAX.
Get one result row per group with GROUP BY.
Count conditionally with CASE inside SUM.
Filter groups with HAVING, and explain how it differs from WHERE.
Explain how aggregates treat NULL.

Why this matters

Every dashboard, report card summary, and fee report in the SMS is an aggregate. "Kabir's attendance is 66.67%" is a count divided by a count. "Class 10 paid 65,500" is a sum.

Aggregates are also easy to get subtly wrong. Count the wrong thing, or let a NULL slip into an average, and the principal makes decisions on a wrong number. This lesson shows the traps along with the tools.

The concept

Example: the aggregate functions

An aggregate function takes many values and returns one:

Function	Returns	SMS example
`COUNT(*)`	Number of rows	Students in a section
`COUNT(column)`	Number of non-NULL values	Papers that have marks
`SUM(column)`	Total	Fees collected
`AVG(column)`	Average	Average Science marks
`MIN(column)`, `MAX(column)`	Smallest, largest	Lowest and highest marks

Example: GROUP BY

Without GROUP BY, an aggregate returns one row for the whole table. With it, you get one row per group.

Think of sorting attendance slips into trays: one tray per section. Then you count each tray. GROUP BY does the sorting; COUNT does the counting.

Example
SELECT SectionId, COUNT(*) AS Students
FROM dbo.Enrollments
WHERE AcademicYearId = 2
GROUP BY SectionId;

The rule: every column in SELECT must either be in GROUP BY or be inside an aggregate. This query breaks it: it groups by class only, but also asks for the section name, and one class has many section names:

Example
SELECT sec.ClassId, sec.Name, COUNT(*)
FROM dbo.Enrollments AS e
INNER JOIN dbo.Sections AS sec ON sec.Id = e.SectionId
GROUP BY sec.ClassId;

SQL Server cannot know which section name to show for Class 10, so it refuses:

Output
Msg 8120, Level 16, State 1, Line 1
Column 'dbo.Sections.Name' is invalid in the select list because it is not contained in either an aggregate function or the GROUP BY clause.

Example: three ways to count

Form	Counts	10-A Science papers in Unit Test 1
`COUNT(*)`	Every row	4
`COUNT(MarksObtained)`	Rows where the column is not NULL	3, because Meera was absent
`COUNT(DISTINCT SubjectId)`	Different values	1, because all four are Science

Pick the form that matches the question. "How many students sat the paper?" is COUNT(MarksObtained), not COUNT(*).

Example: WHERE vs HAVING

Clause	Filters	Runs	Example
`WHERE`	Rows, before grouping	Before `GROUP BY`	Only this year's enrollments
`HAVING`	Groups, after grouping	After `GROUP BY`	Only sections with fewer than 5 students

WHERE cannot use an aggregate, because the groups do not exist yet. Trying it gives:

Output
Msg 147, Level 15, State 1, Line 1
An aggregate may not appear in the WHERE clause unless it is in a subquery contained in a HAVING clause or a select list, and the column being aggregated is an outer reference.

The full order of a query is now: FROM, WHERE, GROUP BY, HAVING, SELECT, ORDER BY.

Example: reading an aggregate query aloud

Read an aggregate query in the order SQL Server runs it, and it explains itself. For the "small sections" query below:

FROM and JOIN: take every enrollment with its section.
WHERE: keep only this year's enrollments.
GROUP BY: put them into one pile per class and section.
HAVING: keep only piles with fewer than 5 enrollments.
SELECT: for each remaining pile, show the class, the section, and the count.
ORDER BY: sort the piles by class, then section.

If you cannot say a query this way, it is probably doing something you did not intend.

Apply it to the SMS

Save the full script below as queries\018-group-by.sql.

Example: section sizes

Students in sections A and B of Class 6 and Class 10 this year, grouped by class and section:

Example
SELECT
    sec.ClassId,
    sec.Name AS Section,
    COUNT(*) AS Students
FROM dbo.Enrollments AS e
INNER JOIN dbo.Sections AS sec ON sec.Id = e.SectionId
INNER JOIN dbo.AcademicYears AS ay ON ay.Id = e.AcademicYearId
WHERE ay.IsCurrent = 1
  AND sec.ClassId IN (6, 10)
  AND sec.Name IN ('A', 'B')
GROUP BY sec.ClassId, sec.Name
ORDER BY sec.ClassId, sec.Name;

ClassId	Section	Students
6	A	4
6	B	2
10	A	4
10	B	2

Example: attendance percentage with CASE

CASE returns a value based on a condition: CASE WHEN test THEN a ELSE b END. Inside SUM, it counts only the rows you want. Present and Late count as present:

Example
SELECT
    s.FirstName,
    COUNT(*) AS DaysRecorded,
    SUM(CASE WHEN a.Status IN ('Present', 'Late') THEN 1 ELSE 0 END) AS DaysPresent,
    CAST(100.0 * SUM(CASE WHEN a.Status IN ('Present', 'Late') THEN 1 ELSE 0 END) / COUNT(*) AS DECIMAL(5, 2)) AS AttendancePercent
FROM dbo.AttendanceRecords AS a
INNER JOIN dbo.Enrollments AS e ON e.Id = a.EnrollmentId
INNER JOIN dbo.Students AS s ON s.Id = e.StudentId
WHERE e.SectionId = 19
  AND e.AcademicYearId = 2
GROUP BY s.FirstName, e.RollNumber
ORDER BY e.RollNumber;

FirstName	DaysRecorded	DaysPresent	AttendancePercent
Aarav	3	3	100.00
Diya	3	3	100.00
Kabir	3	2	66.67
Meera	3	3	100.00

Why 100.0 and not 100? Dividing two whole numbers gives a whole number in SQL Server: SELECT 2 / 3 returns 0, while 2.0 / 3 returns 0.666666. Starting with 100.0 makes the whole calculation decimal.

Example: fees per class

Five tables joined, then summed per class:

ClassName	Payments	Collected
Class 6	1	39500.00
Class 10	2	65500.00

Class 10's 65,500 is Aarav's 45,500 plus Diya's part payment of 20,000.

Example: HAVING

Small sections this year: fewer than 5 students. There are 220 sections; HAVING keeps only the groups that pass:

Example
SELECT
    sec.ClassId,
    sec.Name AS Section,
    COUNT(*) AS Students
FROM dbo.Enrollments AS e
INNER JOIN dbo.Sections AS sec ON sec.Id = e.SectionId
WHERE e.AcademicYearId = 2
GROUP BY sec.ClassId, sec.Name
HAVING COUNT(*) < 5
ORDER BY sec.ClassId, sec.Name;

It returns the same four sections as the first query: 6-A, 6-B, 10-A, and 10-B. This time nothing told it where to look; every other section has 50 students.

Example: aggregates skip NULL

Unit Test 1 Science for 10-A and 10-B. Meera was absent, so her MarksObtained is NULL:

Papers	PapersWithMarks	AverageMarks	Lowest	Highest
6	5	79.000000	61.00	91.00

COUNT(*) counts all 6 rows. COUNT(MarksObtained) counts only the 5 with marks.

AVG also skips NULL: (88 + 91 + 72 + 61 + 83) / 5 = 79. That is usually what you want, because an absent student did not score zero.

Example: aggregating 600,000 rows

The whole school's attendance, by status:

Status	Records
Present	528033
Absent	36001
Late	18001
Leave	18001

SQL Server reads all 600,036 rows to answer this, in well under a second on a laptop. In Module 5, you will see when such queries get slow, and how indexes help.

Show queries/018-group-by.sql

queries/018-group-by.sql
-- Lesson 18: GROUP BY and aggregate functions. Read-only: it changes nothing.
USE SchoolDb;
GO

-- 1. COUNT per group: students in each section A and B of Class 6 and Class 10 this year.
SELECT
    sec.ClassId,
    sec.Name AS Section,
    COUNT(*) AS Students
FROM dbo.Enrollments AS e
INNER JOIN dbo.Sections AS sec ON sec.Id = e.SectionId
INNER JOIN dbo.AcademicYears AS ay ON ay.Id = e.AcademicYearId
WHERE ay.IsCurrent = 1
  AND sec.ClassId IN (6, 10)
  AND sec.Name IN ('A', 'B')
GROUP BY sec.ClassId, sec.Name
ORDER BY sec.ClassId, sec.Name;

-- 2. CASE inside SUM: attendance percentage for each student in 10-A (Present and Late count).
SELECT
    s.FirstName,
    COUNT(*) AS DaysRecorded,
    SUM(CASE WHEN a.Status IN ('Present', 'Late') THEN 1 ELSE 0 END) AS DaysPresent,
    CAST(100.0 * SUM(CASE WHEN a.Status IN ('Present', 'Late') THEN 1 ELSE 0 END) / COUNT(*) AS DECIMAL(5, 2)) AS AttendancePercent
FROM dbo.AttendanceRecords AS a
INNER JOIN dbo.Enrollments AS e ON e.Id = a.EnrollmentId
INNER JOIN dbo.Students AS s ON s.Id = e.StudentId
WHERE e.SectionId = 19
  AND e.AcademicYearId = 2
GROUP BY s.FirstName, e.RollNumber
ORDER BY e.RollNumber;

-- 3. SUM: fees collected per class this year.
SELECT
    c.Name AS ClassName,
    COUNT(p.Id) AS Payments,
    SUM(p.Amount) AS Collected
FROM dbo.FeePayments AS p
INNER JOIN dbo.FeeInvoices AS i ON i.Id = p.FeeInvoiceId
INNER JOIN dbo.Enrollments AS e ON e.Id = i.EnrollmentId
INNER JOIN dbo.Sections AS sec ON sec.Id = e.SectionId
INNER JOIN dbo.Classes AS c ON c.Id = sec.ClassId
WHERE p.IsDeleted = 0
GROUP BY c.Name, c.DisplayOrder
ORDER BY c.DisplayOrder;

-- 4. HAVING: small sections this year (fewer than 5 students).
SELECT
    sec.ClassId,
    sec.Name AS Section,
    COUNT(*) AS Students
FROM dbo.Enrollments AS e
INNER JOIN dbo.Sections AS sec ON sec.Id = e.SectionId
WHERE e.AcademicYearId = 2
GROUP BY sec.ClassId, sec.Name
HAVING COUNT(*) < 5
ORDER BY sec.ClassId, sec.Name;

-- 5. Aggregates and NULL: Unit Test 1 Science in 10-A and 10-B (Meera was absent).
SELECT
    COUNT(*) AS Papers,
    COUNT(m.MarksObtained) AS PapersWithMarks,
    AVG(m.MarksObtained) AS AverageMarks,
    MIN(m.MarksObtained) AS Lowest,
    MAX(m.MarksObtained) AS Highest
FROM dbo.Marks AS m
WHERE m.ExamSubjectId = 9
  AND m.EnrollmentId BETWEEN 13 AND 18;

-- 6. The whole school: attendance records by status (over 600,000 rows).
SELECT
    Status,
    COUNT(*) AS Records
FROM dbo.AttendanceRecords
GROUP BY Status
ORDER BY Records DESC;
GO

Common mistakes

A column in SELECT that is not grouped. Error 8120. Fix: add it to GROUP BY, or wrap it in an aggregate.
An aggregate in WHERE. Error 147. Fix: move the condition to HAVING.
Whole-number division. 3 / 4 is 0. Fix: multiply by 100.0 first, or cast to DECIMAL.
Counting after a one-to-many join. Totals double. Fix: check counts, or aggregate before joining (Lesson 19).
Treating NULL as zero without deciding to. AVG skips NULLs. Fix: decide what absent means for the report, and write it explicitly.

In real projects

From 16 years of .NET projects

Before I trust any dashboard number, I recompute it a second way. If attendance is 91%, then present plus late plus absent plus leave must add up to the total rows. If it does not, a join or a filter is wrong, and it is better that I find it than the principal.

Definitions matter more than SQL here: does "Late" count as present, and do holidays count? I write the definition next to the query, and agree it with the school once.

Every report then uses the same definition. Most "the numbers don't match" meetings are really two reports with two definitions.

Best practices

Name every aggregate column with AS.
Use 100.0 or a CAST to avoid whole-number division.
Filter rows in WHERE and groups in HAVING.
Decide and document how NULLs should count in each report.
Cross-check totals with a second, simpler query.
Choose COUNT(*), COUNT(column), or COUNT(DISTINCT column) to match the exact question.

Recap

COUNT, SUM, AVG, MIN, MAX turn many rows into one value.
GROUP BY gives one result row per group.
Every selected column must be grouped or aggregated.
WHERE filters rows before grouping; HAVING filters groups after.
CASE inside SUM counts only the rows you want.
Aggregates skip NULL, except COUNT(*).
Read a query in run order to check it does what you mean.

Homework

Library module task: For every book, show how many copies the library has and how many are available right now.

A correct result looks like: three rows. "The Jungle Book" has 1 copy and 0 available, because Advika still has it.

Show the solution

library-assignment/018-library-group-by.sql
-- Library homework, Lesson 18. Read-only.
USE SchoolDb;
GO

-- Copies per book, and how many are available.
SELECT
    b.Title,
    COUNT(*) AS Copies,
    SUM(CASE WHEN c.Status = 'Available' THEN 1 ELSE 0 END) AS Available
FROM dbo.Books AS b
INNER JOIN dbo.BookCopies AS c ON c.BookId = b.Id
GROUP BY b.Title
ORDER BY b.Title;
GO

Quiz

Which clause filters groups after GROUP BY?
1. WHERE
2. HAVING
3. ORDER BY
4. FROM
Show answer
Answer: B. HAVING HAVING runs after grouping, so it can use aggregates.
Six papers exist, one with NULL marks. What does COUNT(MarksObtained) return?
1. 6
2. 5
3. 1
4. NULL
Show answer
Answer: B. 5 COUNT(column) counts only non-NULL values.
What does SELECT 2 / 3 return in SQL Server?
1. 0.67
2. 0
3. 1
4. An error
Show answer
Answer: B. 0 Whole number divided by whole number gives a whole number.
Why does SELECT sec.ClassId, sec.Name, COUNT(*) ... GROUP BY sec.ClassId fail?
1. COUNT needs a column
2. sec.Name is neither grouped nor aggregated
3. GROUP BY needs HAVING
4. ClassId is not a number
Show answer
Answer: B. sec.Name is neither grouped nor aggregated Every selected column must be in GROUP BY or inside an aggregate.
What does SUM(CASE WHEN Status = 'Absent' THEN 1 ELSE 0 END) calculate?
1. The number of days
2. The number of absent records
3. The attendance percentage
4. The number of students
Show answer
Answer: B. The number of absent records Each absent row adds 1, every other row adds 0.

Interview questions

Questions are grouped by experience level. Click a question to see a model answer. All questions: SQL Server interview questions.

Fresher (0-2 yrs)

F What is the difference between WHERE and HAVING? concept

WHERE filters rows before they are grouped, and cannot use aggregates. HAVING filters groups after GROUP BY, so it can use aggregates, such as HAVING COUNT(*) < 5 to find small sections.

See the example · Permalink

F What are the main aggregate functions? concept

COUNT counts rows or values, SUM adds them, AVG averages them, and MIN and MAX return the smallest and largest. With GROUP BY, each returns one value per group, such as students per section or fees per class.

See the example · Permalink

F Why does a query fail with 'column is invalid in the select list because it is not contained in either an aggregate function or the GROUP BY clause'? concept

Every column in SELECT must either be in GROUP BY or be inside an aggregate. Otherwise one group could have several values for that column, and SQL Server cannot choose one. Add the column to GROUP BY or aggregate it.

See the example · Permalink

Mid (3-5 yrs)

M What is the difference between COUNT(*), COUNT(column), and COUNT(DISTINCT column)? concept

COUNT(*) counts every row. COUNT(column) counts rows where that column is not NULL, so absent students without marks are skipped. COUNT(DISTINCT column) counts different values. Four 10-A Science papers give 4, 3, and 1 respectively.

See the example · Permalink

M How do you count only some rows inside a GROUP BY query? coding

Put a CASE inside SUM: SUM(CASE WHEN Status IN ('Present', 'Late') THEN 1 ELSE 0 END) adds 1 for each matching row and 0 for others. Divided by COUNT(*) and multiplied by 100.0, it gives an attendance percentage per student.

See the example · Permalink

M Why does 100 * 2 / 3 give 66 instead of 66.67 in SQL Server? concept

When both sides of a division are whole numbers, SQL Server returns a whole number and drops the fraction. Make one side decimal, for example 100.0 * 2 / 3, or CAST a value to DECIMAL, to keep the fraction.

See the example · Permalink

Senior (6-9 yrs)

S The class average looks too high because absent students are left out. How do you handle it? scenario

AVG skips NULLs, so absent students do not count. That is usually right, but the school must decide the rule. If absent should count as zero, use AVG(ISNULL(MarksObtained, 0)); if not, show the number of papers averaged next to the average so readers know.

How to answer: Explain how aggregates treat NULL, then turn it into a business decision with the school. Show both SQL forms. Recommend documenting the definition with the report so every report uses the same rule.

See the example · Permalink

S Two reports show different attendance percentages for the same class. How do you resolve it? scenario

Compare the definitions first: which statuses count as present, which dates are included, and whether deleted or transferred students are counted. Then recompute the number by hand from raw rows. Agree one definition with the school and make both reports use it.

How to answer: Show that you treat it as a definitions problem before a SQL problem. Walk through the checks, reconcile with raw counts, and close with prevention: one shared, documented definition, ideally in one view used by every report.

See the example · Permalink

Architect / Lead (10+ yrs)

A The principal's dashboard aggregates millions of rows on every page load. How would you redesign it? design

Pre-compute the numbers: a summary table updated as data changes or on a short schedule, or an indexed view that SQL Server keeps up to date. The dashboard then reads a few rows instead of millions, at the cost of extra work on writes or slightly delayed numbers.

How to answer: Quantify the load first (rows scanned, page loads per minute). Compare indexed views, summary tables, and caching on freshness, write cost, and complexity. Choose based on how fresh the principal really needs the numbers to be.

See the example · Permalink

Resources

Code for this lesson: git tag lesson-18-group-by (code repository coming soon)
Files: queries/018-group-by.sql, library-assignment/018-library-group-by.sql
Video: coming soon

What's next

Some questions need a query inside a query: "students above the class average". Lesson 19: Subqueries and CTEs

Want live sessions, code reviews, and interview preparation? Join the next batch at Sahasra Technologies.

Introduction​

Why this matters​

The concept​

Example: the aggregate functions​

Example: GROUP BY​

Example: three ways to count​

Example: WHERE vs HAVING​

Example: reading an aggregate query aloud​

Apply it to the SMS​

Example: section sizes​

Example: attendance percentage with CASE​

Example: fees per class​

Example: HAVING​

Example: aggregates skip NULL​

Example: aggregating 600,000 rows​

Common mistakes​

In real projects​

Best practices​

Recap​

Homework​

Quiz​

Interview questions​

Fresher (0-2 yrs)​

Mid (3-5 yrs)​

Senior (6-9 yrs)​

Architect / Lead (10+ yrs)​

Resources​

What's next​

Introduction

Why this matters

The concept

Example: the aggregate functions

Example: GROUP BY

Example: three ways to count

Example: WHERE vs HAVING

Example: reading an aggregate query aloud

Apply it to the SMS

Example: section sizes

Example: attendance percentage with CASE

Example: fees per class

Example: HAVING

Example: aggregates skip NULL

Example: aggregating 600,000 rows

Common mistakes

In real projects

Best practices

Recap

Homework

Quiz

Interview questions

Fresher (0-2 yrs)

Mid (3-5 yrs)

Senior (6-9 yrs)

Architect / Lead (10+ yrs)

Resources

What's next