﻿ SQL - Departments which contains at least one employee

# 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;
``````

## Query Visualization:

Duration:

Rows:

Cost:

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

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