I have a database table with employees (emp) with colomns name and salary. By using an inline view query i would like to list name, salary and a new colomn with each employees % of the total salary of all employees (salary/tot_sal*100). I am having trouble understanding the use of views. I tried the following code, but it did not work. Any ideas?
create view tot_sal as
select sum(sal);
select name, salary, salary/tot_sal*100
from tot_sal
CodePudding user response:
You have SELECT twice and a semi-colon. The name for the view cannot be the same as the table. You have to name each column. Try the following
create view tot_sal_view as select
sum(sal) sum_salary,
name,
salary,
salary/tot_sal*100
from tot_sal;
CodePudding user response:
Try:
create view tot_sal_view as select name,salary, salary/(select sum(salary) from employees e)*100
from employees;
Given example:
CREATE TABLE employees (
name varchar(50) ,
salary decimal(10,2)
);
INSERT INTO employees VALUES
('Steven',24000.00),
('Neena',17000.00),
('Lex',17000.00),
('Alexander',9000.00),
('Bruce',6000.00),
('David',4800.00),
('Valli',4800.00),
('Diana',4200.00),
('Nancy',12000.00),
('Daniel',9000.00),
('John',8200.00),
('Ismael',7700.00),
('Jose Manuel ',7800.00),
('Luis', 6900.00),
('Den',11000.00),
('Alexander',3100.00),
('Shelli',2900.00);
Result:
name salary salary/(select sum(salary) from employees e)*100
Steven 24000.00 15.444015
Neena 17000.00 10.939511
Lex 17000.00 10.939511
Alexander 9000.00 5.791506
Bruce 6000.00 3.861004
David 4800.00 3.088803
Valli 4800.00 3.088803
Diana 4200.00 2.702703
Nancy 12000.00 7.722008
Daniel 9000.00 5.791506
John 8200.00 5.276705
Ismael 7700.00 4.954955
Jose Manuel 7800.00 5.019305
Luis 6900.00 4.440154
Den 11000.00 7.078507
Alexander 3100.00 1.994852
Shelli 2900.00 1.866152