How to get max salary and count from employee table-CodePudding

I have task where I need to filter in such a way that need to print the max(salary * months) and number of times the max(salary * months) occurred.

My data:

employee_id , name , months , salary
12          , Ami  , 15     , 1230
14          , Bik  , 1      , 678
15          , Tom  , 10     , 500
16          , Bob  , 12     , 3456
17          , Wil  , 1      , 9000
18          , Tim  , 14     , 1239
22          , Jil  , 15     , 1230

Note: the table as only 4 columns -> employee_id, name, months, salary

I have written a query where I am able to get max(salary * months)

Query #1:

select 
    max(msal) 
from 
    (select 
         employee_id, 
         name, 
         salary, 
         months, 
         (salary * months) as msal 
     from 
         employee);

Query #2:

select 
    e1.employee_id, 
    e1.months, 
    e1.salary, 
    e2.employee_id, 
    (e2.salary * e2.months) as earnings 
from 
    employee e1 
join 
    employee e2 on e1.employee_id = e2.employee_id;

Query #3:

select 
  emp,earnings 
from 
  (
    select 
      e1.employee_id as emp, 
      e1.months, 
      e1.salary, 
      e2.employee_id, 
      (e2.salary * e2.months) as earnings 
    from 
      employee e1 
      join employee e2 on e1.employee_id = e2.employee_id
  ) 
order by 
  earnings desc;

I calculated on paper these details:

15  * 1230   ->  18450
1   * 678    ->  678
10  * 500    ->  5000
12  * 3456   ->  41472
1   * 9000   ->  9000
14  * 1239   ->  17346
15  * 1230   ->  18450

According my analysis my output should be: 41472 1

41472-> max(salary * months)
1     -> occurred twice

CodePudding user response：

Here's one option:

Sample data:

SQL> with emp (employee_id, name, months, salary) as
  2    (select 12, 'ami', 15, 1230 from dual union all
  3     select 14, 'bik', 1 ,  678 from dual union all
  4     select 15, 'tom', 10,  500 from dual union all
  5     select 16, 'bob', 12, 3456 from dual union all
  6     select 17, 'wil', 1 , 9000 from dual union all
  7     select 18, 'tim', 14, 1239 from dual union all
  8     select 22, 'jil', 15, 1230 from dual
  9    ),

Query begins here: rank values in descending order ...

 10  temp as
 11    (select salary * months max_value,
 12            count(*) cnt,
 13            rank() over (order by salary * months desc) rnk
 14     from emp
 15     group by salary * months
 16    )
 17  select max_value, cnt
 18  from temp
 19  where rnk = 1;           --> ... and, finally, fetch row(s) that rank as the highest

 MAX_VALUE        CNT
---------- ----------
     41472          1

SQL>

Although you didn't ask for it, but - such an approach is good because you can easily get - for example:

the second highest value simply by modifying the where clause to
```
where rnk = 2
```
top 3 values
```
where rnk <= 3
```

etc.

CodePudding user response：

Finally I was able to write a Query

Incase any efficient way of writing Query. Please provide your solution

Query :

select 
  earnings, 
  count(emp) 
from 
  (
    select 
      e1.employee_id as emp, 
      e1.months, 
      e1.salary, 
      e2.employee_id, 
      (e2.salary * e2.months) as earnings 
    from 
      employee e1 
      join employee e2 on e1.employee_id = e2.employee_id
  ) 
where 
  earnings = (
    select 
      max(salary * months) 
    from 
      employee
  ) 
group by 
  earnings 
order by 
  earnings desc;

CodePudding user response：

You want to look at salary * month results, count their occurence and only show the top figure. So, group by salary * month, count and take the top row:

select salary * month, count(*)
from employee
group by salary * month
order by salary * month desc
fetch first row only;