I have the following table:
id | underlying | option_symbol | option_type | expiration_date | strike | mid |
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
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!