SQL Aggregations SQL Topic exerciseHardVerified answerSQLite + PostgreSQL + MySQL + SQL Server live · 1 guided

Department Payroll Contribution

Return one payroll-contribution row per staffed department using all non-NULL employee salaries as the denominator cohort.

  • Joins
  • Subqueries
  • Aggregation
  • Numeric functions
  • NULL handling

Exercise brief

Understand the request

Finance operations lead The budget review needs department payroll contributions that reconcile to the same company-wide employee population.

Calculate each staffed department’s share of one explicitly defined company-wide payroll cohort.

Return

  • Return department_name, total_salary_cost, share_pct in this exact left-to-right order.

Constraints

  • The numerator is non-NULL salary within the department; the denominator is non-NULL salary across all employees.
  • Calculate percentage as department payroll divided by company-wide payroll, multiplied by 100 and rounded to 2 decimals.
  • Protect a zero or NULL company payroll with NULLIF rather than inventing a percentage.
  • Displayed shares may total 99.99 or 100.01 because each row is rounded independently.
  • Sort by share_pct descending.

Data you will use

Review the relevant tables before deciding how to join, filter, or aggregate them.

departments

  • department_idINTEGER
  • department_nameVARCHAR(50)

employees

  • salaryINTEGER
  • department_idINTEGER

Hints, when you need them

Open one clue at a time so you still do the reasoning.

Hint 1

Write down the numerator cohort and denominator cohort separately before calculating the percentage.

Hint 2

Calculate one company-wide payroll total, reuse it for every department row, and protect it with NULLIF.

Hint 3

WITH company_payroll AS ( SELECT SUM(/* denominator measure */) AS total_payroll FROM employees WHERE /* denominator population */ ) SELECT d.department_name, SUM(/* numerator */) AS total_salary_cost, ROUND(/* numerator divided by protected denominator */, 2) AS share_pct FROM /* staffed department population */ GROUP BY /* department grain */;

Verified SQL answer

Attempt the problem first, then compare structure and reasoning—not just syntax.

Reveal solution and explanation
WITH company_payroll AS (SELECT SUM(salary) AS total_payroll FROM employees WHERE salary IS NOT NULL) SELECT d.department_name, SUM(e.salary) AS total_salary_cost, ROUND(SUM(e.salary) * 100.0 / NULLIF((SELECT total_payroll FROM company_payroll), 0), 2) AS share_pct FROM departments d INNER JOIN employees e ON d.department_id = e.department_id WHERE e.salary IS NOT NULL GROUP BY d.department_name ORDER BY share_pct DESC;

Why this works

Correctness: each department numerator is divided by the same company-wide non-NULL salary denominator, so the shares describe one coherent cohort. Edge case: independent two-decimal rounding makes the displayed shares total 99.99 here, and a zero or NULL denominator returns NULL. Portability: the scalar/CTE form is broadly portable; PostgreSQL can express the same metric with a nested aggregate window denominator.

Success check

Each staffed department appears once and the displayed shares reconcile to the company-wide denominator within rounding tolerance.

Expected result

Use this output to verify values, aliases, ordering, and row count.

department_nametotal_salary_costshare_pct
IT37500047.59
Marketing16000020.3
Finance13800017.51
HR11500014.59

Learn the concepts behind this answer

Strengthen your understanding with these targeted learning topics:

Continue practicing

Build the next SQL skill

SQL Practice Online →

Open the interactive workspace and practice across SQL topics.