Average Salary by Department

Mediumsql

Write a query that returns, for each department, the department name and the average salary of its employees as avg_salary, using integer division (SUM(salary) DIV COUNT(*), which drops any remainder — do not use AVG(), whose fractional result won't match). Order the results by avg_salary descending.

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

Sample data (this is what your query runs against when you press Run):

sql
INSERT INTO employees (id, name, department, salary, manager_id) VALUES
(1, 'Asha', 'Engineering', 85000, NULL),
(2, 'Rohan', 'Engineering', 72000, 1),
(3, 'Neha', 'Sales', 68000, NULL),
(4, 'Vikram', 'Sales', 61000, 3),
(5, 'Priya', 'Engineering', 79000, 1),
(6, 'Karan', 'Marketing', 55000, NULL),
(7, 'Divya', 'Marketing', 58000, 6),
(8, 'Aman', 'Sales', 64000, 3);

Example 1

Input

(none)

Output

department	avg_salary
Engineering	78666
Sales	64333
Marketing	56500

GROUP BY department collapses rows per department; SUM(salary) DIV COUNT(*) computes the truncated integer average (MySQL's / returns a decimal, DIV truncates). Reference: SELECT department, SUM(salary) DIV COUNT(*) AS avg_salary FROM employees GROUP BY department ORDER BY avg_salary DESC;

Average Salary by Department

Related Problems

Input (stdin)

Output