python - SQLite filter on a renamed column not working

Question

I'm trying to apply a filter on a renamed field while selecting it but it doesn't work

here's the table of transactions I want to filter:

id      stock   buy sell    price       trans_date              buy_sell
2       AAPL    10  0       141.77      28-Jan-2021 04:59:58    Buy
2       AAPL    5   0       141.77      28-Jan-2021 04:59:19    Buy
2       SSSS    5   0       14.83       28-Jan-2021 04:59:47    Buy
2       NFLX    5   0       522.8       28-Jan-2021 05:00:11    Buy
2       MSFT    5   0       232.88      28-Jan-2021 05:01:47    Buy
2       NFLX    2   0       522.8       28-Jan-2021 04:59:37    Buy
2       SSSS    0   2       14.83       28-Jan-2021 05:00:52    Sell
2       SSSS    0   3       14.83       28-Jan-2021 05:02:47    Sell
2       AAPL    0   4       141.83      28-Jan-2021 05:06:18    Sell
2       AAPL    0   2       141.77      28-Jan-2021 05:06:18    Sell

And below is the query which I'm Trying to run

SELECT stock, sum(buy - sell) as share, trans_date, avg(price) as price FROM stocks 
WHERE user_id = 2  GROUP BY stock;

I'm trying to get all data displayed but to not display whichever stock with 0 like the ssss tock below

stock   share   trans_date              price
AAPL    9       28-Jan-2021 04:59:19    141.77
MSFT    5       28-Jan-2021 05:01:47    232.88
NFLX    7       28-Jan-2021 04:59:37    522.8
SSSS    0       28-Jan-2021 04:59:47    14.83

Any suggestions? to remove that SSSS from the filtered table ?

question from:https://stackoverflow.com/questions/65952436/sqlite-filter-on-a-renamed-column-not-working

Answer 1

Use having clause as follows:

SELECT stock, sum(buy - sell) as share, trans_date, avg(price) as price FROM stocks 
WHERE user_id = 2  GROUP BY stock
having sum(buy - sell) <> 0 -- or sum(buy) = sum(sell)