Published / updated29 Aug 2026

Functions and Expressions

Before you start

You need: CRUD and filtering (Article 04).

Time: about 45 minutes, plus the practice.

Learning objective

Produce correct calculated values and summaries, knowing exactly how each function treats NULL.

Topics

• Aggregate functions and NULL
• String functions
• Date functions
• CASE expressions
• ISNULL, COALESCE, NULLIF
• Conversion: CAST, CONVERT, TRY_CONVERT
• Window functions — a first look

Aggregate functions

Function	Returns	NULL handling
COUNT(*)	Row count	Counts every row
COUNT(col)	Non-null count	Skips NULL
COUNT(DISTINCT col)	Distinct non-null count	Skips NULL
SUM(col)	Total	Skips NULL; returns NULL if all are NULL
AVG(col)	Mean	Skips NULL — does not treat it as 0
MIN / MAX	Extremes	Skips NULL

SELECT COUNT(*)               AS TotalResults,
       COUNT(MarksObtained)   AS ResultsWithMarks,
       AVG(MarksObtained)     AS AverageMarks,
       MAX(MarksObtained)     AS HighestMarks,
       MIN(MarksObtained)     AS LowestMarks
FROM   dbo.ExamResult
WHERE  ExamId = 5;

The AVG trap

Absent students have MarksObtained = NULL. AVG skips them, so the average is over students who actually sat the exam — usually correct.

But when the intent is "class average including absentees as zero", the query must say so:

-- Average of those who sat the exam
SELECT AVG(MarksObtained) FROM dbo.ExamResult WHERE ExamId = 5;

-- Average across the whole class, absent counted as zero
SELECT AVG(ISNULL(MarksObtained, 0)) FROM dbo.ExamResult WHERE ExamId = 5;

These give different numbers, and both appear on real report cards. Decide which you mean and write it explicitly — never leave it to the default.

The same applies to SUM. Summing an all-NULL column returns NULL, not 0, which then makes any arithmetic on the result NULL:

SELECT ISNULL(SUM(p.Amount), 0) AS TotalCollected
FROM   dbo.FeePayment AS p
WHERE  p.FeeAccountId = 42;

Without ISNULL, an account with no payments returns NULL and the outstanding calculation becomes NULL rather than the full amount due.

Integer division again

-- Wrong: both are INT, so this is 0 for anyone below 100%
SELECT MarksObtained / MaxMarks * 100 AS Percentage

-- Correct
SELECT CAST(MarksObtained AS DECIMAL(9,4)) / MaxMarks * 100 AS Percentage

String functions

Function	Does
LEN(s)	Length, excluding trailing spaces
DATALENGTH(s)	Bytes, including trailing spaces
LEFT(s, n) / RIGHT(s, n)	First or last n characters
SUBSTRING(s, start, len)	Portion — start is 1-based
CHARINDEX(find, s)	Position of find, or 0
REPLACE(s, old, new)	Replace all occurrences
LTRIM / RTRIM / TRIM	Strip spaces
UPPER / LOWER	Case
CONCAT(a, b, ...)	Join, treating NULL as empty
CONCAT_WS(sep, ...)	Join with a separator
STRING_AGG(col, sep)	Combine rows into one string
REPLICATE(s, n)	Repeat
FORMAT(v, fmt)	Format — slow, avoid in bulk

SELECT s.RollNumber,
       s.Name,
       LEFT(s.RollNumber, 3)                    AS SchoolCode,
       SUBSTRING(s.RollNumber, 5, 4)            AS AdmissionYear,
       RIGHT(s.RollNumber, 4)                   AS Sequence,
       CONCAT_WS(' - ', s.ClassName, s.Section) AS ClassSection
FROM   dbo.Student AS s;

CONCAT versus +

SELECT s.Name + ', ' + s.Address         FROM dbo.Student AS s;  -- NULL when Address is NULL
SELECT CONCAT(s.Name, ', ', s.Address)   FROM dbo.Student AS s;  -- 'Ravi Kumar, '

+ propagates NULL through the whole expression. CONCAT treats NULL as an empty string. Losing a whole name because the address is missing is a common report defect — prefer CONCAT, or wrap each nullable part in ISNULL.

CONCAT_WS skips NULL parts entirely, including the separator, which is usually what an address line needs:

SELECT CONCAT_WS(', ', s.Address, s.City, s.PinCode) AS FullAddress
FROM   dbo.Student AS s;

LEN and trailing spaces

SELECT LEN('Ravi   ');          -- 4
SELECT DATALENGTH('Ravi   ');   -- 7 (or 14 for NVARCHAR)

LEN ignores trailing spaces. A validation of LEN(ParentPhone) = 10 passes for '9951510727 '. Use TRIM on input, and check DATALENGTH when hunting for stray whitespace in imported data.

STRING_AGG

SELECT   sub.ClassName,
         STRING_AGG(sub.Name, ', ') WITHIN GROUP (ORDER BY sub.Name) AS Subjects
FROM     dbo.Subject AS sub
WHERE    sub.SchoolId = 1
GROUP BY sub.ClassName;

Turns many rows into one delimited string — useful for a "subjects taught" column. WITHIN GROUP (ORDER BY ...) makes the order deterministic; without it the order is arbitrary.

Date functions

Function	Returns
SYSDATETIME()	Server local date and time, high precision
SYSUTCDATETIME()	UTC — use this for stored timestamps
GETDATE()	Legacy local DATETIME
CAST(SYSDATETIME() AS DATE)	Today's date only
DATEADD(part, n, d)	Add an interval
DATEDIFF(part, a, b)	Difference in whole units
DATEPART(part, d)	One component as an int
YEAR / MONTH / DAY	Shorthand
EOMONTH(d)	Last day of that month
DATEFROMPARTS(y, m, d)	Build a date
ISDATE(s)	1 if convertible

SELECT s.Name,
       s.DateOfBirth,
       DATEDIFF(YEAR, s.DateOfBirth, CAST(SYSDATETIME() AS DATE)) AS ApproxAge
FROM   dbo.Student AS s;

DATEDIFF counts boundaries, not elapsed time

SELECT DATEDIFF(YEAR, '2009-12-31', '2010-01-01');   -- 1

One day apart, and DATEDIFF(YEAR, ...) returns 1 — it counts how many year boundaries were crossed. Age calculated this way is wrong for anyone whose birthday has not yet occurred this year.

Correct age:

SELECT s.Name,
       DATEDIFF(YEAR, s.DateOfBirth, CAST(SYSDATETIME() AS DATE))
         - CASE
             WHEN DATEADD(YEAR,
                          DATEDIFF(YEAR, s.DateOfBirth, CAST(SYSDATETIME() AS DATE)),
                          s.DateOfBirth) > CAST(SYSDATETIME() AS DATE)
             THEN 1
             ELSE 0
           END AS Age
FROM   dbo.Student AS s;

The same boundary behaviour applies to MONTH, DAY, and the rest.

Date ranges, again

-- Wrong: excludes payments made during 30 June
WHERE p.PaidOn BETWEEN '2024-06-01' AND '2024-06-30'

-- Wrong: correct results, but no index seek
WHERE YEAR(p.PaidOn) = 2024 AND MONTH(p.PaidOn) = 6

-- Correct
WHERE p.PaidOn >= '2024-06-01' AND p.PaidOn < '2024-07-01'

For a whole month from a parameter:

DECLARE @monthStart DATE = '2024-06-01';

SELECT SUM(p.Amount) AS Collected
FROM   dbo.FeePayment AS p
WHERE  p.PaidOn >= @monthStart
  AND  p.PaidOn <  DATEADD(MONTH, 1, @monthStart);

CASE

SELECT s.Name,
       r.MarksObtained,
       CASE
           WHEN r.IsAbsent = 1                 THEN 'Absent'
           WHEN r.MarksObtained >= 90          THEN 'A+'
           WHEN r.MarksObtained >= 80          THEN 'A'
           WHEN r.MarksObtained >= 70          THEN 'B'
           WHEN r.MarksObtained >= sub.PassingMarks THEN 'C'
           ELSE 'Fail'
       END AS Grade
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;

Order matters. Conditions are evaluated top to bottom and the first TRUE wins. The absent check must come first, or an absent student with NULL marks falls through to ELSE 'Fail' and is recorded as failing an exam they never sat.

Always include ELSE. Without it, an unmatched row returns NULL silently.

The simple form compares one expression:

SELECT CASE s.Status
           WHEN 0 THEN 'Active'
           WHEN 1 THEN 'Inactive'
           WHEN 2 THEN 'Graduated'
           WHEN 3 THEN 'Transferred'
           ELSE 'Unknown'
       END AS StatusText
FROM   dbo.Student AS s;

CASE inside an aggregate is how you produce conditional counts in one pass:

SELECT   s.ClassName,
         COUNT(*)                                          AS Total,
         SUM(CASE WHEN r.IsAbsent = 1 THEN 1 ELSE 0 END)   AS Absent,
         SUM(CASE WHEN r.IsAbsent = 0
                   AND r.MarksObtained >= 35 THEN 1 ELSE 0 END) AS Passed
FROM     dbo.ExamResult AS r
JOIN     dbo.Student AS s ON s.Id = r.StudentId
GROUP BY s.ClassName;

ISNULL, COALESCE, NULLIF

SELECT ISNULL(s.Address, 'Not recorded')                    AS Address,
       COALESCE(s.ParentPhone, s.AlternatePhone, 'None')    AS Contact,
       NULLIF(s.Section, '')                                AS Section
FROM   dbo.Student AS s;

NULLIF(a, b) returns NULL when the two are equal. Its most useful job is preventing divide-by-zero:

-- Errors when MaxMarks is 0
SELECT r.MarksObtained * 100.0 / e.MaxMarks AS Percentage

-- Returns NULL instead of erroring
SELECT r.MarksObtained * 100.0 / NULLIF(e.MaxMarks, 0) AS Percentage

A single bad row with MaxMarks = 0 otherwise fails the entire report with "Divide by zero error encountered".

Conversion

SELECT CAST('87' AS INT),
       CAST(87.6 AS INT),                        -- 87, truncated not rounded
       CONVERT(VARCHAR(10), SYSDATETIME(), 105), -- dd-mm-yyyy
       TRY_CAST('abc' AS INT),                   -- NULL, no error
       TRY_CONVERT(DATE, '31/02/2024');          -- NULL, no error

CAST is standard SQL. CONVERT is SQL Server-specific and adds date format styles.

Use TRY_CAST and TRY_CONVERT on any data you did not create. Plain CAST on a bad value fails the whole statement — one malformed row in an import kills the entire batch. The TRY_ versions return NULL for that row and let the rest through, so you can find the bad data:

SELECT RawRollNumber
FROM   dbo.StudentImport
WHERE  TRY_CAST(RawDateOfBirth AS DATE) IS NULL
  AND  RawDateOfBirth IS NOT NULL;

That query lists exactly the rows that will fail, before you run the import.

Note CAST to INT truncates. Use ROUND when you mean to round:

SELECT ROUND(87.6, 0),      -- 88.0
       CEILING(87.1),       -- 88
       FLOOR(87.9);         -- 87

Window functions — a first look

Window functions calculate across a set of rows without collapsing them, which is the difference from GROUP BY.

SELECT   s.Name,
         s.ClassName,
         r.MarksObtained,
         RANK()       OVER (PARTITION BY s.ClassName ORDER BY r.MarksObtained DESC) AS ClassRank,
         AVG(r.MarksObtained) OVER (PARTITION BY s.ClassName)                       AS ClassAverage
FROM     dbo.ExamResult AS r
JOIN     dbo.Student    AS s ON s.Id = r.StudentId
WHERE    r.ExamId = 5 AND r.IsAbsent = 0
ORDER BY s.ClassName, ClassRank;

Every student row is preserved, with their rank within their class and their class average alongside. A GROUP BY would have reduced this to one row per class.

Function	Gives
ROW_NUMBER()	1, 2, 3, 4 — no ties
RANK()	1, 2, 2, 4 — ties share, then a gap
DENSE_RANK()	1, 2, 2, 3 — ties share, no gap
SUM/AVG/COUNT OVER	Aggregate without collapsing
LAG / LEAD	Previous or next row's value

For a class ranking, DENSE_RANK is usually what a school means: two students tied for second are both second, and the next is third.

ROW_NUMBER also gives an alternative paging method and a way to deduplicate — both covered in the grouping article.

Errors you will hit

Message	Cause	Fix
SUM returns NULL	No rows matched	ISNULL(SUM(Amount), 0)
Divide by zero error encountered	Denominator is zero or NULL	NULLIF(x, 0)
COUNT(*) and COUNT(column) differ	COUNT(column) skips NULL	Choose deliberately
Class average looks too low	Absentees stored as 0 and included	Store absent as NULL; AVG then excludes them
Conversion failed when converting date and/or time	Ambiguous or invalid date string	Use ISO format
String comparison misses a match	Trailing spaces	LTRIM(RTRIM(x)), or check with '[' + x + ']'

AVG ignores NULL — and that is exactly what you want for absentees. It is also why storing 0 for an absence quietly corrupts every average.

Common mistakes

• Assuming AVG treats NULL as zero
• SUM returning NULL for no rows, then propagating through a calculation
• INT / INT division returning 0
• + for concatenation with a nullable column, losing the whole value
• LEN ignoring trailing spaces during validation
• DATEDIFF(YEAR, ...) used as age
• BETWEEN on a date-time range
• CASE with the absent check after the grade checks
• CASE with no ELSE, returning NULL silently
• Dividing without NULLIF and hitting divide-by-zero
• CAST instead of TRY_CAST on imported data
• FORMAT() in a query over many rows — it is very slow
• RANK where DENSE_RANK was meant

Practice

Write and run these against the School Management System data:

1. Exam summary for one exam: total results, count with marks, average excluding absentees, and average counting absentees as zero. Explain the difference between the last two numbers.
2. Correct age for each student, verified against at least one student whose birthday has not yet passed this year.
3. Roll number split into school code, admission year, and sequence.
4. Full address using CONCAT_WS, for students where some address parts are NULL. Compare with the + version.
5. Grade for every result, with absent handled first. Deliberately move the absent check to the end and confirm absent students now show as Fail.
6. Class-wise summary using SUM(CASE WHEN ...): total, absent, passed, failed — in one pass.
7. Percentage using NULLIF on MaxMarks. Insert one exam with MaxMarks = 0 and confirm the query survives it.
8. Class ranking with ROW_NUMBER, RANK, and DENSE_RANK side by side over data containing a tie. Write down which one a school should use and why.
9. Monthly fee collection for June 2024 using a sargable date range.
10. Subjects per class as a comma-separated list with STRING_AGG.

Then run the AI drill from the course — ask an assistant to generate test data, then verify it. Have it produce 50 student rows, then check: are all roll numbers unique per school, do any dates of birth make a student the wrong age for their class, are section values within your CHECK constraint, and does any row violate a constraint you defined? Record what it got wrong.

You can now

• Use aggregate, string and date functions correctly
• Guard SUM and AVG against NULL and empty sets
• Say why COUNT(*) and COUNT(column) differ
• Explain how absentees affect an average
• Find trailing whitespace that breaks a comparison

Review questions

1. What is the difference between AVG(MarksObtained) and AVG(ISNULL(MarksObtained, 0))?
2. Why is DATEDIFF(YEAR, DateOfBirth, GETDATE()) not a person's age?
3. Why does CONCAT behave differently from + when a column is NULL?
4. Why must the absent condition come first in a grading CASE?

Next: Joins