COUNT(*) or COUNT(column)?

What do you actually want to count?

COUNT(*) counts all perimeter lines. COUNT(telephone) only counts lines where telephone is not NULL.

Using both in the same query produces a completeness check: total population, values entered and difference.

The query explained

Start by defining what a row of the result should represent. This decision determines the necessary columns, groupings, and controls.

The following example isolates the main mechanism. Run it, observe the result, then change one clause at a time to understand its effect.

SELECT
  COUNT(*) AS clients_total,
  COUNT(telephone) AS telephones_renseignes,
  COUNT(*) - COUNT(telephone) AS telephones_absents
FROM clients;

Read the result like an analyst

The three columns must be reconciled: those entered plus absent equals the total. This control makes the final rate explainable.

After a join, COUNT(*) can also count multiplied lines. Always check the level of detail before interpreting the volume.

• Define the population before counting.
• Measure the NULL of the counted column.
• Check the effect of joins.
• Compare detailed and aggregate volume.

Errors that distort the analysis

A query can be valid without correctly answering the question. The most costly errors are often silent: wrong perimeter, multiplied line or missing value interpreted as zero.

Before sharing the result, compare it to a small hand-calculated sample and keep the scope definition with the query.

• Present COUNT(column) as the number of records returned.
• Forget the NULL.
• Count after a many-to-many join.
• Use DISTINCT to hide an missing multiplication.

Get into practice

Use the completeness exercise to calculate a rate and protect the division with NULLIF.

The SQL simulator of SQL.tn only runs read queries on dummy data in your browser. You can try multiple writes and compare their results without installing any software.