Topic 48 of 52 · Beginner
Count known salaries by department
Return total employees and known salaries for each department.
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).
- Group by department.
- Count all rows.
- 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?