I have a query where I need to obtain result from both and perform minus on it

My task

• Actual - calculate average salary from employee table
• Mis - calculate average salary from employee table where salary does not have zero at end
• Result : Actual - Mis

Below are the queries I tried.

Query #1:

with actual as (
select avg(salary) as actual  from employees ac);
,
miscalcualtion as (
select avg(salary) from employees mis WHERE MOD(salary, 10) != 0)

select ac.actual - mis.miscalcualtion  from dual;

I get this error:

ERROR at line 2:
ORA-00928: missing SELECT keyword
SP2-0042: unknown command "," - rest of line ignored.
SP2-0734: unknown command beginning "miscalcual..." - rest of line ignored.
select ac.actual from dual
*
ERROR at line 1:
ORA-00904: "AC"."ACTUAL": invalid identifier

Query #2:

select 
  ac.actual, 
  mis.miscal 
from 
  (
    select 
      avg(salary) as actual 
    from 
      employees ac, 
      (
        select 
          avg(salary) as miscal 
        from 
          employees mis 
        WHERE 
          MOD(salary, 10) != 0
      );

No result obtained from query #2.

Query #3:

select 
  (
    (
      select 
        avg(salary) 
      from 
        employees
    ) minus (
      select 
        avg(salary) 
      from 
        employees 
      WHERE 
        MOD(salary, 10) != 0
    )
  ) as Calculation 
from 
  dual;

Any solution is much appreciated. Solving question of HackerRank

CodePudding user response：

You have some syntax problems with your first queries:

1. using local scoped aliases mis and act outside of scope
2. you don't need to set semicolon in with expression between arguments

The simpliest way to calculate difference between two averages:

select
    (SELECT AVG(salary) as actual from employees)
    -
    (SELECT AVG(salary) FROM employees WHERE MOD(salary, 10) != 0)

CodePudding user response：

I have written the answer and it got passed

Thank you to the author author @Олег Белов

Query

select ceil(avg(salary) - avg(replace(salary,'0',''))) from employees;