Compare Joined Rows to Employee Count — Debugging Queries in SQL

The problem

Scenario: Helix Systems' payroll team reports a total salary figure that is nearly double the expected annual cost. The analyst suspects pairing employees with salaries produces multiple records per employee. To check, the analyst wants the pairing count alongside the total employee count.

Task: Write a query to return two counts: the total number of (employee, salary) pairings on record (joined_row_count), and the total number of employees (employee_count).

Assumptions:

An employee can have multiple salary records on file.
The joined_row_count is the count of (employee, salary) pairings — each salary record paired with its parent employee.
The employee_count is the count of employees.

Output:

One row, holding the two counts.
Columns in this order: joined_row_count, employee_count.

Schema · hr 4 tables

departments

id integer

name text

location text

budget numeric

salaries

id integer

employee_id integer

amount numeric

effective_date date

end_date? date

employees

id integer

name text

email text

department_id integer

manager_id? integer

hire_date date

title text

is_active boolean

job_history

id integer

employee_id integer

title text

department_id integer

start_date date

end_date? date

Check answerShift Ctrl ↵

Run previews · Check grades

Write a query, then run it to see results here.

Worked solution Try it yourself first

Solution query

SELECT
  COUNT(*) AS joined_row_count,
  (
    SELECT
      COUNT(*)
    FROM
      employees
  ) AS employee_count
FROM
  employees e
  JOIN salaries s ON s.employee_id = e.id

The shape

The pairing count from the joined employees and salaries rows sits next to the unmultiplied employee count, produced by an independent scalar subquery. The gap between 116 pairings and 60 employees is exactly the per-employee fanout that's doubling the payroll figure.

Clause by clause

SELECT COUNT(*) AS joined_row_count counts every (employee, salary) pair the join produces. An employee with three salary records on file contributes three rows here, which is how SUM(salary) ends up nearly double the real annual cost downstream.
(SELECT COUNT(*) FROM employees) AS employee_count runs a separate scan over employees and drops the count into the outer row as a second column. This is the parent-side reference number the analyst needs to interpret the joined count.
FROM employees e JOIN salaries s ON s.employee_id = e.id is the same join the payroll report is using. Running it under COUNT(*) instead of SUM(salary) strips away the dollars and exposes the row arithmetic directly.

Why this and not two separate queries

Two queries would return the same numbers, but the analyst would have to mentally hold the first while running the second. One row with both counts makes the fanout factor visible at a glance. 116 pairings over 60 employees is just under 2x — which lines up with payroll being "nearly double" expected, the exact symptom that prompted the diagnostic.

You practiced sizing per-parent fanout — when each employee carries multiple salary records, the pairing count exceeds the employee count, and any employee-level value summed over the pairing set inflates by the per-employee record count.

Return two counts: the total number of (employee, salary) pairings on record (`joined_row_count`), and the total number of `employees` (`employee_count`)

The shape

Clause by clause

Why this and not two separate queries

Reading explains SQL. Writing it, over and over with instant feedback, is what makes you fluent.