When should I use COUNT(*) versus COUNT(column) in SQLite?

First decide what “how many responses” means for your report. If each row represents one submitted response and you want to count every row, use COUNT(*). It includes rows even when answer is NULL. If you want to count only rows that have a non-NULL answer, use COUNT(answer). [S1]

Assume, just for this example, that a table named survey_responses has four rows for one question:

1. Yes
2. No
3. NULL
4. Maybe

This is an illustrative dataset. Its row count is 4; its count of non-NULL answers is 3.

Yes. Select both expressions over the same table:

SELECT
  COUNT(*) AS response_rows,
  COUNT(answer) AS answered_rows
FROM survey_responses;

For the four hypothetical rows above, the result is:

response_rows | answered_rows
4             | 3

The first value counts rows in the input; the second counts rows where answer is non-NULL. SQLite defines COUNT(*) as counting all rows in a group and COUNT(X) as counting occurrences where X is not NULL. [S1] With no GROUP BY, these counts apply to the input dataset as a whole. [S1]

In this example, the difference is one row with a NULL answer. That difference alone does not explain why the value is NULL or how your report should treat it.

Use a grouped query so the counts are calculated within each question’s group. For example, assuming the table has a question_id column:

SELECT
  question_id,
  COUNT(*) AS response_rows,
  COUNT(answer) AS answered_rows
FROM survey_responses
GROUP BY question_id;

For each question group, COUNT(*) counts every row in that group, while COUNT(answer) counts only rows in that group with a non-NULL answer. [S1] These counting rules do not establish what your application considers a valid, submitted, or complete response. Check your table’s meaning before labeling either number “completed responses.”

Name each result for what it actually measures. response_rows counts rows; answered_rows counts non-NULL answer values. If your report needs a different definition—such as counting distinct people or counting answers that are not blank strings—these two expressions do not establish that definition. The SQLite reference explains NULL handling, not distinct-person rules, blank-text handling, or your survey’s business meaning. [S1]

Compare the labels with the rows in your data. In the example, do not describe 4 as “four answers”: one row has a NULL answer. Conversely, do not describe 3 as “three response rows”: all four rows are included in the row count. Keep the query and labels together so readers can see which measure you chose.