SQL Exercises: Departments which contains at least one employee

SQL SUBQUERY: Exercise-22 with Solution

From the following table, write a SQL query to calculate total salary of the departments where at least one employee works. Return department ID, total salary.

Sample table: employees

Sample table: departments

Sample Solution:

SELECT departments.department_id, result1.total_amt 
FROM departments,  
( SELECT employees.department_id, SUM(employees.salary) total_amt  
FROM employees  
GROUP BY department_id) result1 
WHERE result1.department_id = departments.department_id;

Sample Output:

department_id	total_amt
10		4400.00
20		19000.00
30		24900.00
40		6500.00
50		156400.00
60		28800.00
70		10000.00
80		304500.00
90		58000.00
100		51600.00
110		20300.00

N.B. : In certain instances not null is removed in table structure, so results may vary.

Code Explanation:

The said query in SQL that is used to retrieve the department ID and total salary amount for each department in the 'departments' table.
The query first uses a subquery to calculate the total salary for each department. The subquery selects the department ID and salary from the 'employees' table and groups the data by department ID. The sum of the salaries for each group is calculated and given an alias of "total_amt".
The outer query then joins the 'departments' table with the result of the subquery on the "department_id" column.

Visual Presentation:

Alternative Statements:

Using INNER JOIN:

SELECT d.department_id, SUM(e.salary) total_amt
FROM departments d
JOIN employees e ON d.department_id = e.department_id
GROUP BY d.department_id;

Using EXISTS subquery:

SELECT d.department_id,
       SUM(e.salary) total_amt
FROM departments d
JOIN employees e ON d.department_id = e.department_id
GROUP BY d.department_id
HAVING SUM(e.salary) > 0;

Using LEFT JOIN:

SELECT d.department_id, COALESCE(SUM(e.salary), 0) total_amt
FROM departments d
LEFT JOIN employees e ON d.department_id = e.department_id
GROUP BY d.department_id
HAVING COALESCE(SUM(e.salary), 0) > 0;

Practice Online

Query Visualization:

Duration:

Rows:

Cost:

Contribute your code and comments through Disqus.

Previous SQL Exercise: Employees whose salary is more than 3700.
Next SQL Exercise: Employees whose job title is ST_MAN, IT_PROG.