CODEWITHSUNDEEP
sqlmysql-workbenchinline-view
Amarillo
Amarillo

Reputation: 59

Using inline view in SQL

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

Upvotes: 1

Views: 140

Answers (2)

Ergest Basha
Ergest Basha

Reputation: 9018

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

Upvotes: 0

user18098820
user18098820

Reputation:

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;

Upvotes: 0

Related Questions