Version : 11.2
Permanant employees get monthly salary , Contract employees get only commission.
Some permanant employees like PETER and JOHANN get both salary and Commission
I want to find the total amount paid to both permanent and non-permanent employees.
During the total calculation, for employees who get both salary and Commission like PETER and JOHANN, I want to ignore Commission and consider only Salary of them for total pay calculation.
ie. When computing the total amount paid to all these employees
I want to ignore commision of 400 received by PETER
and
I want to ignore commision of 1000 received by JOHANN
ie. The Total paid amount will be: 18,500
create table pay_master (emp_type varchar2(50) , empname varchar2(50), salary number , comm number );
insert into pay_master values ( 'PERM', 'KAREN' , 2000, 0 );
insert into pay_master values ( 'PERM', 'HANS' , 3000, 0 );
insert into pay_master values ( 'CONTRACT', 'KEITH' , 0, 1000 );
insert into pay_master values ( 'CONTRACT', 'ABDUL' , 0, 2000 );
insert into pay_master values ( 'PERM', 'KRISHNA' , 1000, 0 );
insert into pay_master values ( 'CONTRACT', 'CHENG' , 0, 1500 );
insert into pay_master values ( 'PERM', 'PETER' , 5000, 400 );
insert into pay_master values ( 'PERM', 'JOHANN' , 3000, 1000 );
col EMP_TYPE format a10
col EMPNAME format a10
SQL> select * From pay_master order by empname;
EMP_TYPE EMPNAME SALARY COMM
---------- ---------- ---------- ----------
CONTRACT ABDUL 0 2000
CONTRACT CHENG 0 1500
PERM HANS 3000 0
PERM JOHANN 3000 1000 ---------------> Ignore this commision when calculating the total pay
PERM KAREN 2000 0
CONTRACT KEITH 0 1000
PERM KRISHNA 1000 0
PERM PETER 5000 400 ---------------> Ignore this commision when calculating the total pay
8 rows selected.
SQL> select EMPNAME , salary+comm from pay_master order by empname;
EMPNAME SALARY+COMM
---------- -----------
ABDUL 2000
CHENG 1500
HANS 3000
JOHANN 4000
KAREN 2000
KEITH 1000
KRISHNA 1000
PETER 5400
8 rows selected.
SQL> select sum(salary+comm) from pay_master;
SUM(SALARY+COMM)
----------------
19900
Expected output from the SQL with the above mentioned logic is 18500
How can I do this in SQL ? I think I can't use CASE statement here because CASE statement only works within one column. Right?