Difference in Salary in Oracle Select Statement

Question

I have below table having three columns(Empno,Ename,Salary)

Empno  Ename  Salary 
101    Ashok  1000
102    Kumar  2000
103    Mani   3000

I need below Output by displaying difference in salary.

Empno Ename  Salary  Difference_in_Salary
101   Ashok  1000     1000
102   Kumar  2000     1000
103   Mani   3000     1000

Answer 1

There are many ways to calculate a "difference" as it depends what you want the difference from:

SELECT t.*,
       ABS( salary - AVG( salary ) OVER () )                  AS difference_from_avg,
       LEAD( salary ) OVER ( ORDER BY salary ) - salary       AS difference_from_next,
       salary - LAG( salary, 1, 0 ) OVER ( ORDER BY salary )  AS difference_from_prev,
       salary - MIN( salary ) OVER ()                         AS difference_from_min,
       MAX( salary ) OVER () - salary                         AS difference_from_max
FROM   table_name t

Which, for your sample data:

CREATE TABLE table_name ( Empno, Ename, Salary ) AS 
SELECT 101, 'Ashok', 1000 FROM DUAL UNION ALL
SELECT 102, 'Kumar', 2000 FROM DUAL UNION ALL
SELECT 103, 'Mani',  3000 FROM DUAL;

Outputs:

EMPNO | ENAME | SALARY | DIFFERENCE_FROM_AVG | DIFFERENCE_FROM_NEXT | DIFFERENCE_FROM_PREV | DIFFERENCE_FROM_MIN | DIFFERENCE_FROM_MAX
----: | :---- | -----: | ------------------: | -------------------: | -------------------: | ------------------: | ------------------:
  101 | Ashok |   1000 |                1000 |                 1000 |                 1000 |                   0 |                2000
  102 | Kumar |   2000 |                   0 |                 1000 |                 1000 |                1000 |                1000
  103 | Mani  |   3000 |                1000 |                 null |                 1000 |                2000 |                   0

db<>fiddle here