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
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