SQL NULL represents missing or unknown information in many expressions; comparing a value to NULL with equals does not test for missingness. Use the appropriate null predicate and decide what a count is meant to include. This guide uses SQL Server and fictional support tickets.

What does COUNT include?

DECLARE @Tickets TABLE (
    TicketId int PRIMARY KEY,
    Team varchar(10),
    ResolvedAt date NULL
);
INSERT @Tickets VALUES
 (1, 'north', '2026-10-01'),
 (2, 'north', NULL),
 (3, 'south', NULL);

SELECT COUNT(*) AS AllTickets,
       COUNT(ResolvedAt) AS ResolvedTickets,
       SUM(CASE WHEN ResolvedAt IS NULL THEN 1 ELSE 0 END) AS OpenTickets
FROM @Tickets;

The expected row is AllTickets = 3, ResolvedTickets = 1 and OpenTickets = 2. COUNT(*) counts rows, while COUNT(ResolvedAt) counts rows where that expression is not null. Neither result is automatically the “correct total” without a defined question.

Why is ResolvedAt = NULL not the right filter?

Normal equality is not a missing-value test. A comparison involving null can produce UNKNOWN rather than TRUE or FALSE. A WHERE condition keeps rows satisfying its condition; UNKNOWN does not qualify them as true.

Use IS NULL to find missing resolution dates and IS NOT NULL to find present ones. Do not teach that null behaves exactly like an empty string, zero or a special date. Those values carry different information.

What changes when grouping?

-- Continue in the same batch.
SELECT Team,
       COUNT(*) AS AllTickets,
       COUNT(ResolvedAt) AS ResolvedTickets
FROM @Tickets
GROUP BY Team
ORDER BY Team;

North has two tickets and one resolution date. South has one ticket and no resolution date. The count of non-null dates becomes zero for south because an input group exists. A completely absent team would not appear in this query at all.

Does SUM behave like COUNT on empty input?

Do not assume so. An aggregate can return null when no values are available, whereas COUNT has its own defined behavior. If the required interface must return zero, choose that output rule explicitly and apply a suitable expression such as COALESCE where appropriate.

Replacing null with zero is not always harmless. A missing measured amount and a measured amount of zero are different facts. State whether the replacement is a display choice, a reporting convention or part of the underlying data meaning.

What is a conditional count?

A CASE expression can mark rows that satisfy a condition. Summing ones and zeros is one common approach. Counting an expression that becomes non-null only for qualifying rows is another. Explain the chosen expression's behavior for every branch.

A frequent mistake is to count a CASE expression that returns both 1 and 0. Both values are non-null, so COUNT includes both branches. That is not the same as counting only the true branch.

Useful tests for missing-value questions

  • A group with both missing and present values.
  • A group with only missing values.
  • An empty input after filtering.
  • A real zero or empty value that must not be confused with null.

For practice, add a ClosedBy field and define whether a ticket is resolved when a date exists, a person exists or both are required. Different definitions can produce different valid queries. The interview skill is making that definition visible before choosing an aggregate.

References and next practice

learn.microsoft.com — sql select

sql question collection · Browse interview topics · Practise a question in Interview Challenge · How to Answer Technical Interview Questions Out Loud