How to use count() properly and the NULL dilemma

In SQL Queries sometimes the results of a simple count() statement can give you confusing results, here’s why.

Here is a script to create some test data

DROP TABLE IF EXISTS staff

CREATE TABLE staff (
  staff_id INT IDENTITY(1,1),
  name VARCHAR(50) NOT NULL,
  last_review_score INT null
)

INSERT dbo.staff
VALUES
('Derek' ,100),
('James' ,50),
('Alice' ,50),
('Dave' ,null)

SELECT * FROM staff

Now that the staff table is stup and we have some data lets look at whats happens when you use count on the last_review_score column

SELECT COUNT(*) 'count of' FROM staff

Great we get 4 this is a count of the number of rows

SELECT COUNT(last_review_score) 'count of staff with last review scores' FROM staff

Oh dear, we only get 3 because the null values are being ignored. Thus it’s only a count of rows with a value.

SELECT COUNT(DISTINCT last_review_score) 'count of distinct(unique) last review scores' FROM staff

Same problem again there are only 2 valid distinct values them being 100 and 50 the NULL is ignored.