1

I am having some issues with using inline view to create a column where I calculate the salary/total_sal * 100. My problem is that for some reason I am not getting the value for all employees, but only for the first.

 select emp.ename, emp.sal, 
    ( select (emp.sal / sum(emp.sal) * 100))  
    from emp;

I have tried to look around to see if I can find the answer, but I was unable to. Any help would be very helpful!

Chris
  • 45
  • 6

1 Answers1

0

Well, the query in the question should give you a syntax error, since the subquery does not have a from clause. You only need to get the sum in the subquery, not the entire percentage calculation:

select emp.ename,
       emp.sal,
       emp.sal / (select sum(emp2.sal) from emp as emp2) * 100 
from emp;

The alternative is to move the subquery into the from clause (derived table) and do a cross join:

select emp.ename,
       emp.sal,
       emp.sal / t.tot_sal * 100 
from emp
join (select sum(emp2.sal) as tot_sal from emp as emp2) as t;
Shadow
  • 33,525
  • 10
  • 51
  • 64
  • Thank you so much! I have worked almost nothing with SQL and it sometimes drives me mad, cause I fail to understand the logic. – Chris Jan 31 '18 at 00:38