USING ALIAS IN SUBQUERY

Aaron Douglas

Total Post:20

Points:140
Posted by  Aaron Douglas
MYSQL 
 1935  View(s)
Ratings:
Rate this:
Hi Expert!

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?

  1. AVADHESH PATEL

    Post:604

    Points:4228
    Re: Using alias in subquery

    Not sure on your query (you appear to be doing a GROUP BY on a field from a LEFT JOINed table, which could be null for non found matching rows), but maybe cross join to a subselect to get the total of all prices

    SELECT total_quantity, total_cost, cost_per_share, trades.ticker, tickers.code, total_cost/total_of_prices
    FROM
    (
        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
    ) Sub1
    CROSS JOIN
    (
        SELECT SUM(price) as total_of_prices 
        FROM trades 
        WHERE quantity > 0
    ) Sub2
    ORDER BY tickers.code

Answer

NEWSLETTER

Enter your email address here always to be updated. We promise not to spam!