← SQL roadmap

Topic 48 of 52 · Beginner

Count known salaries by department

Return total employees and known salaries for each department.

BasicEntry level · 0–1 yearsAggregation

MySQL 8-compatible SELECT syntax; sample queries also execute in SQLite 3. Window functions require MySQL 8+.

Problem statement

Return total employees and known salaries for each department.

Distinguish headcount from salary-report completeness.

Input: employees table.

Output: dept, total, known in department order.

  • Departments are non-null.
  • Each employee occurs once.

Sample 1

Input: A:100,NULL
Output: A,2,1

COUNT(salary) ignores NULL.

Sample 2

Input: B:60
Output: B,1,1

The known salary count equals headcount.

Brute-force solution

Correlated count subqueries can recount each department repeatedly.

O(n²) without useful indexes or decorrelation.

CREATE TABLE employees(id INTEGER PRIMARY KEY, dept VARCHAR(10), salary INTEGER);
INSERT INTO employees VALUES (1,'A',100),(2,'A',100),(3,'A',80),(4,'B',60),(5,'B',NULL);
SELECT DISTINCT e.dept,(SELECT COUNT(*) FROM employees x WHERE x.dept=e.dept) total,(SELECT COUNT(salary) FROM employees x WHERE x.dept=e.dept) known FROM employees e ORDER BY dept;
Optimized solution

Group once and use COUNT(*) alongside COUNT(salary).

  1. Group by department.
  2. Count all rows.
  3. Count non-null salaries.
CREATE TABLE employees(id INTEGER PRIMARY KEY, dept VARCHAR(10), salary INTEGER);
INSERT INTO employees VALUES (1,'A',100),(2,'A',100),(3,'A',80),(4,'B',60),(5,'B',NULL);
SELECT dept, COUNT(*) total, COUNT(salary) known FROM employees GROUP BY dept ORDER BY dept;
Complexity analysis

Time: O(n) expected hash aggregation or O(n log n) sort aggregation.

Auxiliary space: O(g) group state, engine-dependent.

Test cases
  • Empty table.
  • All salary values missing in a department.
Expected: (A,3,3), (B,2,1).
Common mistakes
  • Replacing NULL with zero before counting known values.
  • Grouping at employee grain.
Key takeaways
  • Headcount and completeness are different.
  • COUNT(column) omits NULL.
  • One row per group.
Follow-up challenges
  • How would you compute completeness percent?
  • How does COUNT(DISTINCT salary) differ?
Practise SQL with the AI mentor

Can't find a SQL topic you need for exams or placements? Request it and we'll add it.