jodu - 1 year ago 131

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

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!

Recommended from our users: **Dynamic Network Monitoring from WhatsUp Gold from IPSwitch**. ** Free Download**