Data Engineering Associate with Databricks Practice Exam

How do you count rows in SQL while ignoring NULL values?

1. COUNT(*)

2. COUNT(column_name)

3. COUNT_IF(column_name IS NULL)

4. COUNT(NULL)

The correct answer is: COUNT(column_name)

The choice that correctly counts rows while ignoring NULL values is the option that specifies a column name. When using COUNT(column_name), SQL counts only the non-NULL entries in that specified column. This function effectively filters out any rows where the designated column has NULL values. In contrast, using COUNT(*) would count all rows in a table, including those with NULL values. The COUNT_IF function is not a standard SQL function; instead, SQL typically uses a conditional clause in a CASE statement to achieve similar results, but COUNT_IF wouldn't be recognized by standard SQL. Lastly, COUNT(NULL) does not serve a purpose because COUNT will never return any rows; it’s a function that counts non-NULL values, and feeding it NULL doesn't yield any count. Thus, COUNT(column_name) is the appropriate function when the goal is to count only the rows where the specified column has valid (non-NULL) data.