Substract two database entries based on value of a certain field
As RDFozz has pointed out in his comments, this solution only works if:


There is one OPEN and one CLOSE entries
Both entries has the same type, 'LONG' or 'SHORT'





select case when t1.datetime &gt; t2.datetime 
            then t1.datetime 
            else t2.datetime end as datetime,
       t1.orderid,
       t1.type,
       case when t1.type = 'SHORT' 
            then t1.price - t2.price 
            else t2.price - t1.price end as diff
from   tbl t1
join   tbl t2
on     t1.orderid = t2.orderid
and    t1.type = t2.type
and    t2.action = 'CLOSE'
where  t1.action = 'OPEN'



datetime   | orderid | type | diff
:--------- | :------ | :--- | ---:
2018-01-03 | a34hsbf | LONG | 0.24



dbfiddle here