Skip to Main Content

SQL & PL/SQL

Announcement

For appeals, questions and feedback about Oracle Forums, please email oracle-forums-moderators_us@oracle.com. Technical questions should be asked in the appropriate category. Thank you!

SQL for salary calculation

J.KiechleSep 21 2012 — edited Sep 21 2012
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?
This post has been answered by Frank Kulash on Sep 21 2012
Jump to Answer
Comments
Locked Post
New comments cannot be posted to this locked post.
Post Details
Locked on Oct 19 2012
Added on Sep 21 2012
5 comments
4,707 views