ArenaTotal Salary Expenditure by DepartmentEasy

Total Salary Expenditure by Department

EasySQLPractice library · +10 XPaggregatessqlgroup by

A company has several departments, and each employee is assigned to one department. You need to find out how much the company spends on salaries for each department.

Tables:

employees (employee_id INT PRIMARY KEY, name VARCHAR(100), salary NUMERIC(10, 2), department_id INT)
departments (department_id INT PRIMARY KEY, department_name VARCHAR(100))

Return the department_name and the total salary spent by that department. The results should be ordered by department_name in ascending order.

Handle NULL department_id in employees by excluding them from any department's total. If a department has no employees, it should not appear in the results. Ties in department names are not possible as department_id is the primary key.

Tables

CREATE TABLE departments (
    department_id INTEGER PRIMARY KEY,
    department_name VARCHAR(100) NOT NULL
);

CREATE TABLE employees (
    employee_id INTEGER PRIMARY KEY,
    name VARCHAR(100) NOT NULL,
    salary NUMERIC(10, 2) NOT NULL,
    department_id INTEGER
);

Example data

INSERT INTO departments (department_id, department_name) VALUES
(1, 'Engineering'),
(2, 'Sales'),
(3, 'Marketing'),
(4, 'HR');

INSERT INTO employees (employee_id, name, salary, department_id) VALUES
(101, 'Alice', 75000.00, 1),
(102, 'Bob', 80000.00, 1),
(103, 'Charlie', 60000.00, 2),
(104, 'David', 55000.00, 2),
(105, 'Eve', 70000.00, 3),
(106, 'Frank', 50000.00, NULL);

Your query is also checked on hidden datasets with different data.

Total Salary Expenditure by Department

Tables

Example data

Hints

Similar problems