Click here to Skip to main content
14,639,458 members
Rate this:
Please Sign up or sign in to vote.
See more:
hi, i want to Find sum of two maximum salary from employee table in oracle.
my employee table as

EMP_ID               EMP_NAME              CONTACTNO     SALARY
-------------------- -------------------- ---------- ----------
Emp01                Mohan Chandra        9560498289      16500 
Emp02                Dinesh               9856895689      21000 
Emp03                Sachin               7858968956      18000 
Emp04                Ajay                 9136564555       9500 
Emp05                Sneha                7585963212      10625 
Emp06                Rahul                9163568958      11300 
Emp07                Divya                7539895689       8500 
Emp08                Nisha                9758695645      11485 




now i am writing query as
create table temp  AS SELECT salary  from EMPLOYEE
where rownum<3 order by salary desc;

select sum(salary) from TEMP;


but it is showing result 37500 instead of 39000.
when i written query to see temp table so it shows
 SALARY
----------
     21000 
     16500 

instead of
 SALARY
----------
     21000 
     18000 


so what should i do for this.
i want to find sum of two maximum salary from employee table.

my another question is i want to select empname,salary, bonus.and bonus column will be three months of salary per employees.so what should i do?please give me solution.

    EMP_NAME          Salary     Bonus
-------------- ----------            ----------
         Mohan Chandra     16500      49500
         Dinesh            21000      63000
   .
   .
   .
   .
   .
and so on
Posted
Updated 7-Jan-16 20:20pm
v2
Comments
Mohibur Rashid 7-Jan-16 8:23am
   
Why do you need temporary table?
Try something similar
'Select sum(a) from (select sal as a from table order by sal desc limit 2)'
Mohibur Rashid 7-Jan-16 8:24am
   
For second part, i would be far more work
Member 10192073 8-Jan-16 1:59am
   
ya actually i am using oracle but limit will be in my sql,
but your logic helped me its working fine.
in oracle it is
select sum(salary) from (select salary from employee order by salary desc)
where rownum<3;

we can do this by using temp table also like as

create table temp as select salary from(select salary from employee order by
salary desc)where rownum<=2;

select sum(salary) from temp;
drop table temp;

1 solution

Rate this:
Please Sign up or sign in to vote.

Solution 1

Why to force doors wide open?

Oracle well know RANK function[^], which provides a way to get k-th value of salary:
select SUM(salary) sumof2salaries from (
  select e.*, row_number() over (order by salary desc) rn
  from emp e
)
where rn in (1, 2);


Other resources:
RANK[^]
IN Condition[^]
sql - How can I select the record with the 2nd highest salary in database Oracle? - Stack Overflow[^]

[EDIT]
If you would like to get salary + bonus for each user for 3 months, you have to change your query this way:
select empname, SUM(e.salary + e.bonus) totalSalary
from emp e
where e.paydate between to_date ('2015/10/01', 'yyyy/mm/dd') and to_date ('2015/12/31', 'yyyy/mm/dd')
group by empname


BETWEEN Conditions[^]
Oracle/PLSQL: BETWEEN Condition[^]
   
v3
Comments
Member 10192073 8-Jan-16 2:04am
   
give me solution of my second question please
Maciej Los 8-Jan-16 2:13am
   
I'd like to help you, but i can't. I can't read direct from your screen or in your mind. You didn't provide any valuable information about time period, which is necessary to get salary + bonus for 3 months. Improve your question (use "Improve question" widget) and i'll promise to point you in right direction.
[edit]
I have made some changes in my solution. I hope that may help you to find solution.
Cheers,
Maciej
Member 10192073 8-Jan-16 2:20am
   
i have improved sir,now give me solution please
Maciej Los 8-Jan-16 2:24am
   
As i mentioned it earlier, i understand what you want to achieve, but there is no information about time period. Please, see updated answer.

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)




CodeProject, 503-250 Ferrand Drive Toronto Ontario, M3C 3G8 Canada +1 416-849-8900 x 100