Sub Query in FROM Clause

View All Scripts Login to Run Script

Script Name Sub Query in FROM Clause
Description The following statement selects from subqueries in the FROM clause and for each department returns the total employees and salaries as a decimal value of all the departments
Area SQL General
Contributor Mike Hichwa (Oracle)
Created Monday October 05, 2015

Statement 1

The following statement selects from subqueries in the FROM clause and for each department returns the total employees and salaries as a decimal value of all the departments.

SELECT a.department_id "Department", 
   to_char(100*a.num_emp/b.total_count,'990D00') "%_Employees", 
   to_char(100*a.sal_sum/b.total_sal,'990D00') "%_Salary" 
FROM 
(SELECT department_id, COUNT(*) num_emp, SUM(salary) sal_sum 
   FROM hr.employees 
   GROUP BY department_id) a, 
(SELECT COUNT(*) total_count, SUM(salary) total_sal 
   FROM hr.employees) b 
ORDER BY a.department_id

 
Department %_Employees %_Salary
10    0.93    0.64
20    1.87    2.75
30    5.61    3.60
40    0.93    0.94
50   42.06   22.62
60    4.67    4.17
70    0.93    1.45
80   31.78   44.04
90    2.80    8.39
100    5.61    7.46
110    1.87    2.94
 -    0.93    1.01

12 rows selected.

Department	%_Employees	%_Salary
10	0.93	0.64
20	1.87	2.75
30	5.61	3.60
40	0.93	0.94
50	42.06	22.62
60	4.67	4.17
70	0.93	1.45
80	31.78	44.04
90	2.80	8.39
100	5.61	7.46
110	1.87	2.94
-	0.93	1.01

Additional Information

Database on OTN SQL and PL/SQL Discussion forums
Oracle Database
Download Oracle Database