How to return a value while calculating between two columns from the same table in postgresql?

Question

I'm trying to write a SQL query for Postgresql where I'm subtracting a value of a column in a row with that of another row in the same table and returning the result. I can use IS NULL in SQL, and IFNULL in sqlite3. If I use coalesce in postgresql it asks me to add those to group by which defeats my purpose. Can someone pls help?

SQLITE3 Query:

SELECT t.ticker, t.company, IFNULL((t.num_shares-t.num_shares),t.num_shares) AS num_shares
FROM buy b JOIN shares sh ON b.ticker=sh.ticker
LEFT JOIN sell s ON b.cuid=s.cuid AND b.ticker=s.ticker
WHERE b.cuid=:cuid
GROUP BY b.ticker, sh.company
ORDER BY sh.company

Postgresql: tried a few variations:

SELECT t.ticker, t.company, COALESCE((t.num_shares-t.num_shares),t.num_shares) AS num_shares
FROM buy b
JOIN shares sh ON b.ticker=sh.ticker
LEFT JOIN sell s ON b.cuid=s.cuid AND b.ticker=s.ticker
where b.cuid=:cuid
GROUP BY b.ticker, sh.company
ORDER BY sh.company

SELECT t.ticker, t.company,
    CASE 
    WHEN t.num_shares-t1.num_shares IS NULL THEN t.num_shares
    ELSE t.num_shares-t1.num_shares
    END AS num_shares
FROM transaction t
LEFT JOIN transaction t1 ON t.cuid=t1.cuid AND t.ticker=t1.ticker
where t.cuid=1
GROUP BY t.ticker, t.company
ORDER BY t.company

These fail because num_shares is not aggregated or in group by. Any help is greatly appreciated. Thank you

Answer 1

I solved it using a couple CTE and CASE WHEN conditions like this, just posting here so anyone run into this sort of issue can refer to this hopefully! :)

WITH bought_shares AS (SELECT cuid, ticker, SUM(num_shares) AS num_bought_shares
                        FROM transaction
                        WHERE cuid=:cuid AND trans_detail='by'
                        GROUP BY cuid, ticker
                        ORDER BY ticker),

                        sold_shares AS (
                            SELECT cuid, ticker, SUM(num_shares) AS num_sold_shares
                            FROM transaction
                            WHERE cuid=:cuid AND trans_detail='sl'
                            GROUP BY cuid, ticker
                            ORDER BY ticker)

                        SELECT t.ticker, t.company,
                            CASE
                                WHEN b.num_bought_shares - s.num_sold_shares IS NULL THEN b.num_bought_shares
                                ELSE b.num_bought_shares - s.num_sold_shares
                            END AS num_curr_shares
                        FROM transaction t
                        LEFT JOIN bought_shares b ON t.cuid=b.cuid AND t.ticker=b.ticker
                        LEFT JOIN sold_shares s ON t.cuid=s.cuid AND t.ticker=s.ticker
                        WHERE t.cuid=:cuid
                        GROUP BY t.ticker, t.company, num_curr_shares
                        ORDER BY t.ticker