Which SQL Query Returns Accurate Employee Head Count
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?
Community Insight
The question tests correct placement of DISTINCT inside an aggregate function, and the trap is picking grouped or syntactically invalid variants that do not yield one overall head count.
This SQL question shows that counting raw rows in the JOBS table double-counts employees who hold multiple jobs, inflating the head count. The page establishes that COUNT(DISTINCT EMPLOYEE_ID) is the only form that tallies each employee exactly once for a single accurate total.
Option C is the most tempting wrong pick because it contains the correct COUNT(DISTINCT ...) syntax, but adding JOB_TYPE breaks the result into one row per job type instead of producing a single company-wide head count (and option A uses invalid COUNT DISTINCT(...) syntax).
Community Discussion (1 comments)
Comments & Corrections
No comments yet — spotted an error or have a note? Share it below.
Expert Analysis
Why the Answer Is Correct
Option D,SELECT COUNT(DISTINCT EMPLOYEE_ID) FROM JOBS, collapses duplicate employee IDs before counting, so an employee holding three jobs contributes exactly one to the total. Because no grouping column is selected, the query returns a single scalar value — the true head count — which is precisely what the requester asked for. This is the canonical SQL idiom for counting unique members of a column in a table with repeating entities.Why the Other Options Are Wrong
Option A placesDISTINCT outside the parentheses as COUNT DISTINCT(EMPLOYEE_ID), which is not valid standard SQL syntax and would fail or behave unexpectedly, and it also groups by JOB_TYPE. Option B applies DISTINCT to the result of the aggregate — DISTINCT COUNT(EMPLOYEE_ID) — which merely removes duplicate count values and still totals every job row, leaving the inflated head count unchanged. Option C uses valid COUNT(DISTINCT EMPLOYEE_ID) syntax but selects JOB_TYPE alongside it, so the output is a per-job-type breakdown of counts rather than one overall employee head count.Community Comment Notes
Amelia233 argued that the correct answer is D, explaining that "The DISTINCT keyword removes duplicate employee IDs, giving an accurate employee head count." That reasoning matches the analysis here: only option D both deduplicates employee IDs and returns a single ungrouped total.Exam Strategy
When a table holds a one-to-many relationship, always ask whether the request wants a total or a breakdown before choosing your aggregate. For totals, place DISTINCT inside the COUNT parentheses and leave grouping columns out of the SELECT list.
Frequently Asked Questions
Why doesn't SELECT DISTINCT COUNT(EMPLOYEE_ID) fix the inflated head count?
DISTINCT applies to the already-computed single total, not to the rows. The count still includes every job row, so duplicates remain.
Why is COUNT(DISTINCT EMPLOYEE_ID) with JOB_TYPE wrong for total head count?
Selecting JOB_TYPE splits the result into one row per job type instead of a single company-wide total, even though the syntax itself is valid.
Related Analysis
Practice All DA0-002 Questions
Access 100 questions with complete answers and detailed explanations.
View Full DA0-002 Practice Test →