CODEWITHSUNDEEP
mysqlsqlinline-view
Chris
Chris

Reputation: 45

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!

Upvotes: 1

Views: 163

Answers (1)

Shadow
Shadow

Reputation: 34285

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;

Upvotes: 0

Related Questions