plsql subtract columns in a same table-CodePudding

I have a simple student table.

name  | amount | vdate
Josh  | 15     | 01.01.2020
Steve | 25     | 05.04.2008
Josh  | 40     | 01.01.2022

What I want to do is subtract Josh value from each other.

I wrote this query but it is not working

   select name , sum(b.amount-a.amount) diff from  
     select name,amount from student a where name = 'Josh' and vdate='01.01.2020'
     union all
     select name,amount from student b where name = 'Josh' and vdate = '01.01.2022')
   group by name

Expected Result is:

name | diff
Josh | 25
Steve| 25

CodePudding user response：

You can try this (I don't know what sense it makes ...): Count the number of rows found until now per fname ("name" is a reserved word and I don't use it). And if the row number obtained this way is odd, then use the negative amount, else the positive amount. Finally, run a sum over these positive/negative rows.

WITH
indata(fname,amount) AS (
            SELECT 'Josh' ,15 
  UNION ALL SELECT 'Steve',25
  UNION ALL SELECT 'Josh' ,40 
)
,
alternate AS (
  SELECT
    fname
  , CASE ROW_NUMBER() OVER(PARTITION BY fname) % 2 
      WHEN 1 THEN amount * -1 -- when odd then negative
      ELSE        amount      -- else positive
    END  AS amount
  FROM indata
)
SELECT
  fname
, ABS(SUM(amount)) AS amount -- absolute value
FROM alternate
GROUP BY fname;
-- out  fname | amount 
-- out ------- --------
-- out  Josh  |     25
-- out  Steve |     25

CodePudding user response：

you can try this code,

    select 
    fname,
    abs(sum(amount2)) amount
from
(
WITH
student(fname,amount,vdate) AS (
            SELECT 'Josh' ,15, to_date('01102017','ddmmyyyy')  from dual
  UNION ALL SELECT 'Steve',25,  to_date('01102017','ddmmyyyy') from dual
  UNION ALL SELECT 'Josh' ,40   ,to_date('01102019','ddmmyyyy')from dual
)


select 
    h.fname,
    h.amount,
    decode((ROW_NUMBER() OVER(PARTITION BY fname order by vdate desc)),1,amount,amount* -1) amount2

    from student h
)
group by 
    fname
    ;

I assume that you get the greater amount value of the person and substract other values, you can select the bigger date instead by modifying the order by clause in the partition window i. e.

decode((ROW_NUMBER() OVER(PARTITION BY fname order by vdate desc)),1,amount,amount * -1) amount2