How to do an arithmetic operation with aliased column in SQL

I have a databse table as like below:

id received_by sent_by amount product_id
1  1           2       10     1
2  1           3       12     1
3  2           1       5      1
4  3           1       8      2

Here, received_by and sent_by are two user ID those who are receiving and sending the product respectively. I want to calculate the total amount of each product of a single user by subtracting the sent amount from received amount. My current query looks like below:

select
    product_id,
    (received - sent) as quantity,
    case(when received_by = 1 then amount end) as received,
    case(when sent_by = 1 then amount end) as sent
group by
    product_id;

Here I get an error that Unknown column 'received' in 'field list'.

How can I calculate each users inventory/stock?

Solution

You can't use the calculated columns in the SELECT list.
Also you need the aggregate function SUM().

One way to do it is with a subquery:

select *, (received - sent) as quantity
from (
  select product_id, 
         sum(case when received_by = 1 then amount else 0 end) as received, 
         sum(case when sent_by = 1 then amount else 0 end) as sent 
  from tablename
  where 1 in (received_by, sent_by)
  group by product_id
) t

Or:

select product_id, 
       sum(case when received_by = 1 then amount else -amount end) as quantity,
       sum(case when received_by = 1 then amount else 0 end) as received, 
       sum(case when sent_by = 1 then amount else 0 end) as sent 
from tablename
where 1 in (received_by, sent_by)
group by product_id

See the demo.