Mysql – Substract two database entries based on value of a certain field

aggregateMySQL

Good evening everyone,

I am looking for the most elegant way to substract two values of one column based on the value in another column. Until now I could only find hacky and unreadable solutions:

I have a table containing entries when orders on an exchange were opened and closed. It looks like this:

|| DATETIME || ORDERID || ACTION || TYPE || PRICE || ...
  2018-01-01   a34hsbf    OPEN      LONG    0.23
  2018-01-02   ghs7fa0    OPEN      SHORT   0.45
  2018-01-03   a34hsbf    CLOSE     LONG    0.47
  ...

Now I want to return a new table containing the differences between entries with the same ORDERID. There can always only be two entries with the same ORDERID. The new table should be indexed by the greater DATETIME of those two entries. Also the difference should take into account if the TYPE was LONG or SHORT. E.g. for LONG a rising price must give a positive difference, for SHORT it must give a negative difference and vice versa.

The new table should look like the following:

|| DATETIME || ORDERID || TYPE || DIFF ||
  2018-01-03   a34hsbf    LONG    0.24
  ...

Many thanks in advance for your help. 🙂

Best Answer

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

Related Question