w3resource

SQL Subquery Exercises: Display the department id and the total salary for those 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.

Pictorial Presentation:

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

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: From the following table, write a SQL query to find any existence of those employees whose salary exceeds 3700. Return first name, last name and department ID.
Next: Write a query to display the employee id, name ( first name and last name ) and the job id column with a modified title SALESMAN for those employees whose job title is ST_MAN and DEVELOPER for whose job title is IT_PROG.

Test your Programming skills with w3resource's quiz.

What is the difficulty level of this exercise?



SQL: Tips of the Day

"where 1=1" statement?

It's usually when folks build up SQL statements.

When you add and value = "Toyota" you don't have to worry about whether there is a condition before or just WHERE.

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