DA0-002 · Question #95
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…
The correct answer is D. SELECT COUNT(DISTINCT EMPLOYEE_ID) FROM JOBS. 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.
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
How the community answered
(31 responses)- A3% (1)
- B6% (2)
- C16% (5)
- D74% (23)
Why each option
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.
`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.
`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.
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.
The `COUNT(DISTINCT EMPLOYEE_ID)` aggregate function counts only the unique occurrences of `EMPLOYEE_ID` in the `JOBS` table. This correctly addresses the problem of employees having multiple job entries by ensuring each individual employee is counted just once, providing an accurate headcount.
Concept tested: SQL COUNT DISTINCT for unique records
Source: https://learn.microsoft.com/en-us/sql/t-sql/functions/count-transact-sql?view=sql-server-ver16
Topics
Community Discussion
No community discussion yet for this question.