Department summary

For each department, show how many employees it has, their average salary and the highest salary.

What to build

A summary of each department: its size, average salary and top salary.

Requirements

• Group by department.
• Use COUNT, ROUND(AVG(...), 2) and MAX on each group.
• Name the columns exactly as asked, and sort by department.

Tables

Each test fills these tables with its own rows, then runs your SQL:

CREATE TABLE employees (
  id INTEGER PRIMARY KEY,
  name TEXT NOT NULL,
  dept TEXT NOT NULL,
  salary INTEGER NOT NULL
);

Output

Columns dept, employees, avg_salary (rounded to 2 decimal places) and top_salary, one row per department, in alphabetical order of dept. Your result is compared with the expected one, column names included, so name each column exactly as asked (use AS).

Constraints

Every employee has a department and a salary.

Example

When a test starts with:

INSERT INTO employees VALUES
  (1, 'Farhana', 'Sales', 40000),
  (2, 'Imran', 'IT', 65000),
  (3, 'Joya', 'Sales', 38000),
  (4, 'Kamal', 'IT', 72000),
  (5, 'Lima', 'HR', 45000);

your SQL should print:

dept   employees  avg_salary  top_salary
-----  ---------  ----------  ----------
HR     1          45000.0     45000
IT     2          68500.0     72000
Sales  2          39000.0     40000
(3 rows)

Why it works

GROUP BY makes one row per department, and the aggregate functions are worked out once for each group.

Examples

CREATE TABLE employees (
  id INTEGER PRIMARY KEY,
  name TEXT NOT NULL,
  dept TEXT NOT NULL,
  salary INTEGER NOT NULL
);
INSERT INTO employees VALUES
  (1, 'Farhana', 'Sales', 40000),
  (2, 'Imran', 'IT', 65000),
  (3, 'Joya', 'Sales', 38000),
  (4, 'Kamal', 'IT', 72000),
  (5, 'Lima', 'HR', 45000);

dept   employees  avg_salary  top_salary
-----  ---------  ----------  ----------
HR     1          45000.0     45000
IT     2          68500.0     72000
Sales  2          39000.0     40000
(3 rows)

CREATE TABLE employees (
  id INTEGER PRIMARY KEY,
  name TEXT NOT NULL,
  dept TEXT NOT NULL,
  salary INTEGER NOT NULL
);
INSERT INTO employees VALUES
  (1, 'Mitu', 'IT', 50000),
  (2, 'Nayeem', 'IT', 50000),
  (3, 'Orin', 'IT', 51000);

dept  employees  avg_salary  top_salary
----  ---------  ----------  ----------
IT    3          50333.33    51000
(1 row)

Each test makes its own tables, runs your SQL on them, and compares what your queries return. Changes never carry over from one test to the next.

Hints

Solution

SELECT dept,
  COUNT(*) AS employees,
  ROUND(AVG(salary), 2) AS avg_salary,
  MAX(salary) AS top_salary
FROM employees
GROUP BY dept
ORDER BY dept;

Lesson: Counting and totals: GROUP BY and HAVING →