Helix Systems' payroll team is auditing salary history. They want to identify employees who have exactly one salary record on file — employees who have never received a raise.
Write a query to return each such employee's ID alongside their salary-record count (which will be 1).
Assumptions:
- The
salariestable contains every salary record (current and historical). - An employee with one salary record has had a single compensation event — typically their initial hire — and no subsequent change.
- The match is on equality with
1, not a comparison.
Output:
- One row per qualifying employee, with columns
employee_idandsalary_count.
Schema · hr 4 tables
Run previews · Check grades
Write a query, then run it to see results here.
Worked solution Try it yourself first
SELECT
employee_id,
salary_count
FROM
(
SELECT
employee_id,
COUNT(*) AS salary_count
FROM
salaries
GROUP BY
employee_id
) AS emp_salary_counts
WHERE
salary_count = 1The shape
The inner query counts salary records per employee; the outer query keeps only the rows where that count is exactly one. The equality filter on the aggregated column is the load-bearing part — these are employees with no compensation history beyond the initial record.
Clause by clause
- The inner block computes one row per employee with their salary-record count:
SELECT employee_id, COUNT(*) AS salary_count
FROM salaries
GROUP BY employee_idEvery employee with a salary row appears once, with their tally next to them.
- FROM (...) AS emp_salary_counts materialises the result as a derived table.
- WHERE salary_count = 1 is the equality match. It picks out exactly the employees whose tally is one — no more, no less. The audit returns 4 employees: 16, 31, 44, and 55.
- SELECT employee_id, salary_count returns each qualifying employee alongside the count. The count column will read 1 on every row by construction, which is the payroll team's confirmation that the filter did what it should.
Why this and not WHERE salary_count > 1 and invert mentally
The equality filter says exactly what the prompt asks: employees with one record. Inverting the question — "everybody except the multi-record employees" — would require either a NOT IN against a separate query or an explicit < 2 comparison. Both work, but neither says the same thing as cleanly. The equality form maps one-to-one onto the prompt's business definition.
The derived table form makes the count a real column for any downstream operation.
You practiced equality-based filtering on an aggregate via a derived table. The recurring rule: WHERE col = 1 outside the derived table works the same way as HAVING COUNT(*) = 1 inside a GROUP BY — same answer, different scaffold.
