Departments With High Total Payroll

Mediumsql

Using a subquery in the FROM clause, write a query returning department and total (sum of salaries) only for departments whose total payroll exceeds 100000, ordered by department.

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

Sample data:

sql
INSERT INTO employees (id, name, department, salary) VALUES
(1, 'Asha', 'Engineering', 90000),
(2, 'Rohan', 'Engineering', 60000),
(3, 'Neha', 'Sales', 70000),
(4, 'Vikram', 'Sales', 50000),
(5, 'Priya', 'Engineering', 75000);

Example 1

Input

(none)

Output

department	total
Engineering	225000
Sales	120000

A derived table (subquery in FROM) must be aliased, then queried like any other table. Reference: SELECT department, total FROM (SELECT department, SUM(salary) AS total FROM employees GROUP BY department) AS dept_totals WHERE total > 100000 ORDER BY department;

Departments With High Total Payroll

Related Problems

Input (stdin)

Output