Which SQL Query Returns Accurate Employee Head Count

DA0-002 By islever Editorial Last updated

Identify data tools Implement quality assurance

Short answer

D SELECT COUNT(DISTINCT EMPLOYEE_ID) FROM JOBS returns one count per unique employee, avoiding double-counting employees who hold multiple jobs.

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?
  1. SELECT JOB_TYPE, COUNT DISTINCT(EMPLOYEE_ID) FROM JOBS
  2. SELECT DISTINCT COUNT(EMPLOYEE_ID) FROM JOBS
  3. SELECT JOB_TYPE, COUNT(DISTINCT EMPLOYEE_ID) FROM JOBS
  4. SELECT COUNT(DISTINCT EMPLOYEE_ID) FROM JOBS Correct Answer

Key Takeaways

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.

Common mistake: 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).

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 places DISTINCT 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.

More DA0-002 FAQ →

Discussion 2 contributions

Compiled from public community threads and reader submissions, lightly edited for clarity. Handles and vote tallies are shown as recorded, and comments reflect the views of individual contributors.

From public community threads 2

  1. 6b7911d 👍 1 Answered D
    D is correct. COUNT(DISTINCT EMPLOYEE_ID) counts each employee once, no matter how many jobs they hold. A is wrong. It selects JOB_TYPE alongside an aggregate without a GROUP BY, so it’s invalid SQL. COUNT DISTINCT(...) is also broken syntax, because DISTINCT has to go inside the parentheses. B is wrong. DISTINCT applies to the result row, not to the IDs being counted. COUNT still tallies every row and returns the same inflated number, and DISTINCT just dedupes that single result. C is wrong. The syntax inside COUNT is right, but adding JOB_TYPE means it would need a GROUP BY. Even with one, it gives a count per job type. Someone with two job types gets counted in both groups, so the total is still inflated, and you don’t get a single head count.
  2. Amelia233 👍 1 Answered D
    The correct answer is D. SELECT COUNT(DISTINCT EMPLOYEE_ID) FROM JOBS. This query counts each employee only once, even if an employee has multiple jobs. The DISTINCT keyword removes duplicate employee IDs, giving an accurate employee head count.

Reader comments & corrections

No reader corrections yet — spotted an error or have a note? Share it below.

Log in to comment, report an error, or add a note about this question.

Which Inconsistency Appears in the January Sales Table?

S-grade

Validating Dashboard Data with Monitoring Alerts

S-grade

Joining Table and API Data Sources with Python

S-grade

Best Communication Approach for Visually Impaired Dashboard Stakeholders

S-grade

Practice All DA0-002 Questions

Access 100 questions with complete answers and detailed explanations.

View Full DA0-002 Practice Test →

← Back to DA0-002 Study Guide