Join With Aggregation: Headcount and Average Salary per Department

Summarize each department’s team size and pay.

Schema

CREATE TABLE departments (
    id        INT PRIMARY KEY,
    dept_name VARCHAR(50) NOT NULL
);

CREATE TABLE employees (
    id      INT PRIMARY KEY,
    name    VARCHAR(50) NOT NULL,
    dept_id INT REFERENCES departments(id),
    salary  NUMERIC(10,2) NOT NULL
);

departments

employees

Task

For each department that has at least one employee, return dept_name, employee_count (number of employees), and avg_salary (average salary rounded to 2 decimals). Order by employee_count descending, then dept_name ascending.

Expected output

Approach

INNER JOIN employees to departments so every employee row carries its department name, then GROUP BY the department. COUNT(*) gives the headcount per group and ROUND(AVG(salary), 2) gives the mean pay. Because it is an INNER JOIN, only departments that actually have employees form a group, which satisfies the at-least-one-employee requirement.

Query

SELECT d.dept_name,
       COUNT(*)                 AS employee_count,
       ROUND(AVG(e.salary), 2)  AS avg_salary
FROM employees AS e
INNER JOIN departments AS d
       ON e.dept_id = d.id
GROUP BY d.dept_name
ORDER BY employee_count DESC, d.dept_name;

Walkthrough

• The join attaches Engineering to Alice and Bob, Sales to Carol, Dave, and Eve, and Marketing to Frank.
• Grouping by dept_name: Engineering has 2 rows averaging (120000 + 100000) / 2 = 110000.00.
• Sales has 3 rows averaging (90000 + 95000 + 85000) / 3 = 90000.00.
• Marketing has 1 row averaging 70000.00.
• ORDER BY employee_count DESC, d.dept_name puts Sales (3) first, then Engineering (2), then Marketing (1).

Complexity & notes

• Grouping requires a hash aggregate or a sort over the joined rows; an index on employees.dept_id speeds the join but not the aggregation itself.
• Pitfall: COUNT(*) counts group rows, while COUNT(e.salary) would skip NULL salaries; pick deliberately. Here salary is NOT NULL so both agree.
• If you needed departments with zero employees to appear (with count 0), use LEFT JOIN departments ... ON from the department side and COUNT(e.id) rather than COUNT(*), since COUNT(*) would return 1 for the all-NULL padded row.
• ROUND in PostgreSQL returns numeric with the requested scale; some dialects spell rounding or decimal formatting differently.