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.
What is the difficulty level of this exercise?
Test your Programming skills with w3resource's quiz.
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
- Weekly Trends
- Python Interview Questions and Answers: Comprehensive Guide
- Scala Exercises, Practice, Solution
- Kotlin Exercises practice with solution
- MongoDB Exercises, Practice, Solution
- SQL Exercises, Practice, Solution - JOINS
- Java Basic Programming Exercises
- SQL Subqueries
- Adventureworks Database Exercises
- C# Sharp Basic Exercises
- SQL COUNT() with distinct
- JavaScript String Exercises
- JavaScript HTML Form Validation
- Java Collection Exercises
- SQL COUNT() function
- SQL Inner Join
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