DA0-002 Question #95: Real Exam Question with Answer & Explanation

Question

A data analyst receives a request for the current employee head count and runs the following SQL statement: SELECT COUNT(EMPLOYEE_ID) FROM JOBS The returned head count is higher than expected because employees can have multiple jobs. Which of the following should return an accurate employee head count?

Options

• ASELECT JOB_TYPE, COUNT DISTINCT(EMPLOYEE_ID) FROM JOBS
• BSELECT DISTINCT COUNT(EMPLOYEE_ID) FROM JOBS
• CSELECT JOB_TYPE, COUNT(DISTINCT EMPLOYEE_ID) FROM JOBS
• DSELECT COUNT(DISTINCT EMPLOYEE_ID) FROM JOBS

Explanation

To accurately count employees when some have multiple jobs, the COUNT(DISTINCT EMPLOYEE_ID) function is required to ensure each unique employee is counted only once.

Common mistakes.

• A. COUNT DISTINCT(EMPLOYEE_ID) is syntactically incorrect; DISTINCT must be inside the COUNT function. Also, including JOB_TYPE would return a count per job type, not a total employee headcount.
• B. SELECT DISTINCT COUNT(EMPLOYEE_ID) FROM JOBS is syntactically incorrect; DISTINCT cannot be applied directly to the result of an aggregate function COUNT in this manner to achieve unique counts of individual rows before aggregation.
• C. While COUNT(DISTINCT EMPLOYEE_ID) is correct, including JOB_TYPE without a GROUP BY clause is syntactically invalid for most SQL dialects, and if GROUP BY JOB_TYPE were added, it would return a distinct employee count per job type, not the total overall headcount.

Concept tested. SQL COUNT DISTINCT for unique records

Reference. https://learn.microsoft.com/en-us/sql/t-sql/functions/count-transact-sql?view=sql-server-ver16

No community discussion yet for this question.