mysqlsqlinline-view

Having problems using inline view for all data

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!

Solution

  • 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;