Postgres calculated columns from joins

Question

I have the following table:

id | underlying | option_symbol | option_type | expiration_date | strike | mid |

I need to perform calculations on joined fields and use those calculated fields for further sorting.

Here is a try to what I mean:

SELECT  a.underlying, 

a.expiration_date, 

(a.strike - b.strike) as spread_profit, 

(a.mid - b.mid) as spread_distance, 

(spread_distance - spread_profit) as spread_max_loss,

(spread_profit / spread_max_loss) as spread_profit_ratio

FROM option_data a

JOIN option_data b ON a.underlying = b.underlying AND a.expiration_date = b.expiration_date

WHERE a.option_type = 'put' 

AND b.option_type = 'call'

AND a.expiration_date <= '2017-10-31' 

group by a.underlying, a.expiration_date order by spread_profit_ratio desc

What should be the right way to formulate this query?

Recommended for you: Get network issues from WhatsUp Gold. Not end users.

Answer 1

You have to substitute the whole expressions, like

SELECT ...,
   (a.strike - b.strike) AS spread_profit, 
   (a.mid - b.mid) AS spread_distance, 
   (a.mid - b.mid - a.strike + b.strike) AS spread_max_loss,
   ((a.strike - b.strike) / (a.mid - b.mid - a.strike + b.strike))
      AS spread_profit_ratio
...

The same holds for the ORDER BY clause, but you can use the shortcut ORDER BY 6 to sort by the sixth SELECT list item.

If that is too cumbersome, you can use subselects:

SELECT underlying, 
       expiration_date,
       spread_profit, 
       spread_distance,
       spread_max_loss,
       spread_profit / spread_max_loss AS spread_profit_ratio
FROM (SELECT underlying, 
             expiration_date,
             spread_profit, 
             spread_distance,
             (spread_distance - spread_profit) AS spread_max_loss
      FROM (SELECT a.underlying, 
                   a.expiration_date,
                   (a.strike - b.strike) as spread_profit, 
                   (a.mid - b.mid) as spread_distance
            FROM ...
           ) sub_1
     ) sub_2
WHERE ...
ORDER BY spread_profit_ratio DESC;

It's your choice!

Postgres calculated columns from joins

Sign up