sqloracle-databaseoracle9i

Difference in Salary in Oracle Select Statement


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

Solution

  • 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