如何根據(jù)另一列求和值，減去別名

SQL-Noob here.

有一堆買賣的物品。

我想按item-name分組，得到一個購買和銷售總額。我還想計算庫存的項(xiàng)目。

我嘗試了一個子查詢：

SELECT NAME,
(
    SELECT SUM(quantity)
    FROM transactions
    WHERE `type` = "Buy") AS "purchases",
(
    SELECT SUM(quantity)
    FROM transactions
    WHERE `type` = "Sell") AS "sales"
FROM transactions
GROUP BY NAME

這會導(dǎo)致錯誤的值：

之后我試了CASE-Statements

SELECT NAME,
SUM(CASE WHEN `type` = "Buy" THEN quantity END) AS "purchases",
SUM(CASE WHEN `type` = "Sell" THEN quantity END) AS "sales"
FROM transactions
GROUP BY name

which works:

問題1：有沒有更好的方法來達(dá)到這個結(jié)果？

問題2：如何計算物品的庫存？

像purchases - sales AS "Stock"這樣的東西似乎不起作用。

Thanks alot