I'm running the following query to get the open positions on a portfolio:
SELECT SUM(trades.quantity) as total_quantity, SUM(trades.price) as total_cost, SUM(trades.price)/SUM(trades.quantity) as cost_per_share,
trades.ticker, tickers.code
FROM (trades)
LEFT JOIN tickers
ON trades.ticker = tickers.id
GROUP BY tickers.code
HAVING total_quantity > 0
ORDER BY tickers.code
I'd like to add an extra column to show the weightening of a position, i.e.:
total_cost/SUM(total_cost) -- Dividing any given position cost by the total cost of the portfolio
Since aliases cannot be used in calculations, I thought I'd need to use a sub-query. I've tried a few things but couldn't make it to work.
Can anyone shed some light on this? Is sub-query the way to go? Is there any other better way to do it?