子查询中使用别名(Using alias in subquery)

我运行以下查询来获取对投资组合的未平仓合约：

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

我想添加一个额外的列显示的位置，即的增重：

total_cost/SUM(total_cost) -- Dividing any given position cost by the total cost of the portfolio

由于别名不能在计算中使用，我想我需要使用一个子查询。 我已经尝试了一些事情，但不能使它工作。

任何人都可以阐明这一些轻？ 是子查询要走的路？ 是否有其他更好的方式来做到这一点？

Answer 1:

您的查询不知道（你出现在从左侧加入了表中的字段，这可能是空非找到符合行做一个GROUP BY），但也许CROSS JOIN到一个子查询得到总的所有价格

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

文章来源: Using alias in subquery