SQL Server: select count

2025-11-18

Consider the following query, what do you expect it to return?

In case the use of VALUES is confusing (it's a way to include data in a query without having to use a table), the values for id that you are considering are 0, 1, 2, 2 and NULL.

SELECT
    COUNT(*),
    COUNT(id),
    COUNT(DISTINCT id)
FROM
    (VALUES
        (0),
        (1),
        (2),
        (2),
        (NULL)
    ) AS _(id)

Answer (click to reveal): 5, 4, 3

Explanation

There are three ways to use COUNT() in a SELECT clause:

COUNT(*) - counts all returned rows, regardless of content
COUNT(id) - counts rows where column id is not null, can also be written as COUNT(ALL id)
COUNT(DISTINCT id) - counts unique values of id that are not null