SQL COUNT Rows: Separate Record Counts From Present Values

The practice task

Summarize a fictional activity log containing a ten-minute session, a measured zero-minute session and a session whose duration is missing. Report both the number of records and the number with measured durations.

The data below is fictional. Run the example in a new SQLite database. It was checked with SQLite 3.51.0; other databases may use different syntax, functions or type rules.

Complete worked query

Create the small fixture and run the query together. The IDs, boundary values and missing entries make the result small enough to check by hand.

CREATE TABLE sessions(duration INTEGER);
INSERT INTO sessions VALUES (10),(0),(NULL);

SELECT COUNT(*) AS records, COUNT(duration) AS measured,
       SUM(duration) AS measured_minutes FROM sessions;

Checked result

This is the result of the exact example above. NULL marks a missing database value rather than the text string "NULL".

Read the result

There are three session records but only two known duration values. The zero-minute session contributes to the measured count. The NULL session contributes to COUNT(*) but not COUNT(duration) or the sum of measured minutes.

These counts answer different operational questions. Three records may describe the logging workload; two measured records describe the available timing data. Calling both values the number of sessions would obscure which population a later average uses.

Counts after a join describe the joined rows. If one session matches several tags, COUNT(*) can exceed the number of original sessions. Inspect the relationship and choose the intended session identifier before deciding whether a distinct count is necessary.

Mistakes to check

Use these checks when adapting the example to a different dataset.

• Do not replace NULL with zero merely to increase a measured count.
• Write a clear alias for each counted population.
• Check whether a join has multiplied the records being counted.