Associate Data Practitioner free question 4 / 10 · Domain 2: Data Analysis and Presentation
A column x has three rows with the values 10, 20, and NULL. What does SELECT COUNT(), COUNT(x) return?
- COUNT(*) is 2 and COUNT(x) is 2
- COUNT(*) is 3 and COUNT(x) is 2
- COUNT(*) is 2 and COUNT(x) is 3
- COUNT(*) is 3 and COUNT(x) is 3
Show the answer and explanation
Answer: B
Why this is correct
The "Function list" on the BigQuery "Aggregate functions" page describes COUNT as: "Gets the number of rows in the input, or the number of rows with an expression evaluated to any value other than NULL." COUNT() counts every row, which is 3, while COUNT(x) counts only the rows where x is not NULL, which is 2.
Why the other options are wrong
"COUNT() is 3 and COUNT(x) is 3" … COUNT(x) leaves NULL values out of the count, so it does not count the row where x has no value. It returns 2, not 3.
"COUNT() is 2 and COUNT(x) is 2" … COUNT() does not skip rows that contain NULL. A row with a NULL in one column is still a row of the table, so it is counted.
"COUNT() is 2 and COUNT(x) is 3" … This reverses what the two forms count. The number of all rows can never be smaller than the number of non-NULL values in one column.
Key point
Confusing the number of rows with the number of filled-in values leads to wrong denominators for missing-data rates and averages. Comparing COUNT() with COUNT(column) tells you how many rows have no value in that column.