How to sum values depending on another column, substract Aliases

Question

SQL-Noob here.

Having a bunch of items which are either bought or sold.

I would like to Group by item-name and get a sum of purchases and sales. Also i would like to calculate the stock of items.

I tried a subquery:

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

which results in wrong values:

After that i tried 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:

Question 1: Is there a better way to achieve the result?

Question 2: How to calculate the Stock of the items?

Something like purchases - sales AS "Stock" doesn't seem to work.

Thanks alot

Answer 1

Use conditional aggregation:

select name,
    sum(case when type = 'Buy'  then quantity else 0 end) as purchases,
    sum(case when type = 'Sell' then quantity else 0 end) as sales,
    sum(case when type = 'Buy'  then quantity else - quantity end) as stock
from transactions
where type in ('Buy', 'Sell')
group by name

The where clause is not necessary of there are just those types in the table.

Side notes:

• using single quotes for literal strings rather than double quotes; this is standard SQL, that all databases understand

• in MySQL, use backticks if you need to quote an identifier rather than double quotes; or better yet, use identifiers that do not require quoting