COUNT and NULL

Until now each output row came from one input row. To answer "what share of members joined through a recommendation?" you need numbers that combine many rows: a numerator and a denominator. A function that takes many rows and returns one value is an aggregate function.

COUNT(column)

count(column) returns the number of rows where that column is not NULL. In the club data, recommended_by is empty for members nobody recommended:

SELECT count(recommended_by) FROM members;

Practice: COUNT skips NULL.

NULL is not zero and not ''

NULL marks a missing value. It is different from 0 and from the empty string ''. To test for it, use IS NULL and IS NOT NULL, never =.

SELECT (0 IS NOT NULL) AND ('' IS NOT NULL);

This returns true: both operands are real values. In the club data, member 40 has a NULL phone and member 17 has a phone of ''. They look alike in a result grid and behave differently in count(phone).

COUNT(*)

When you do not know which column is free of NULL, use count(*). It counts rows, whatever their columns hold.

Practice: Count every member.

Counting a subset with CASE

To count two groups in one pass, wrap each condition in CASE WHEN:

CASE
  WHEN <condition> THEN <result>
  ELSE <result>
END

Do not forget ELSE and END. count() counts every non-NULL value, so a branch that returns 0 still counts. Return NULL for the rows you want to skip:

SELECT
  count(CASE WHEN facility_id = 1 THEN 1 END),
  count(CASE WHEN facility_id = 3 THEN 1 END)
FROM bookings;

Practice: Two counts in one query.

Three ways to count the NULLs

The number of members with no recommender can be found three ways:

SELECT count(*) FROM members WHERE recommended_by IS NULL filters first, then counts.
SELECT count(CASE WHEN recommended_by IS NULL THEN 1 END) FROM members scans once.
SELECT count(*) - count(recommended_by) FROM members counts twice.

All three return the same number. They do different amounts of work, which matters on large tables.