1

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
Stu
  • 30,392
  • 6
  • 14
  • 33
Amarillo
  • 59
  • 6

2 Answers2

0

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

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

Check the demo

Ergest Basha
  • 7,870
  • 4
  • 8
  • 28
  • Thank you! I tried your code but get error code 1050 Table 'tot_sal_view' already exists. Any idea what I did wrong? – Amarillo Feb 12 '22 at 14:17
  • If the view has already been created (but not working properly), change the initial statement to ALTER VIEW (rather than CREATE) – Joe Shark Feb 12 '22 at 16:19
  • @Amarillo the view already exist in your db. As Joe mentioned change the word `CREATE` with `ALTER`. Or you can use `DROP VIEW tot_sal_view ;` and then use my code – Ergest Basha Feb 12 '22 at 16:42