w3resource

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:

SQL Subqueries: Display the department id and the total salary for those departments which contains at least one employee.

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:

Query visualization of Display the department id and the total salary for those departments which contains at least one employee - Duration

Rows:

Query visualization of Display the department id and the total salary for those departments which contains at least one employee - Rows

Cost:

Query visualization of Display the department id and the total salary for those departments which contains at least one employee - 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.

What is the difficulty level of this exercise?

Test your Programming skills with w3resource's quiz.



Follow us on Facebook and Twitter for latest update.

SQL: Tips of the Day

How to combine date from one field with time from another field - MS SQL Server

You can simply add the two.

  • if the Time part of your Date column is always zero
  • and the Date part of your Time column is also always zero (base date: January 1, 1900)

Adding them returns the correct result-

SELECT Combined = MyDate + MyTime FROM MyTable

Ref: https://bit.ly/3wldJYf

 





We are closing our Disqus commenting system for some maintenanace issues. You may write to us at reach[at]yahoo[dot]com or visit us at Facebook