How to find difference between the values of two tables in sql?

Question

I have a table that contains data of a vendor who sells as well as buys products. The table contains the product name, product value, and transaction type ('buy' or 'sell'). I want to write a query to know the profit/loss for every particular product. Table example:

So I am able to find the sum of values corresponding to each product.

select product_name, SUM(product_value) as sell from vendor where transaction_type="sell" group by product_name;
select product_name, SUM(product_value) as buy from vendor where transaction_type="buy" group by product_name;

Now I have these two sub-tables, but how to perform the difference operation between these two tables? I want a resultant table that will have the product_name and profit/loss value (total selling cost - total buying cost) for every product.

Answer 1

Just a guess, but perhaps

with tb1 as (
  select product_name, SUM(product_value) as sell
  from vendor
  where transaction_type='sell'
  group by product_name
),
tb2 as (
  select product_name, SUM(product_value) as buy
  from vendor
  where transaction_type='buy'
  group by product_name
)
select tb1.product_name, tb1.sell, tb2.buy, (tb2.buy - tb1.sell) as profit
from tb1 left join tb2 on tb1.product_name=tb2.product_name

Using this setup:

create table vendor (product_name text, product_value int, transaction_type text)
insert into vendor values ('ABC',3196,'sell'),('XYZ',3196,'sell'),('PQR',3196,'sell'),('ABC',2365,'buy'),('ABC',2365,'buy')

that query returns

product_name  sell   buy profit
         ABC  3196  4730   1534
         PQR  3196    NA     NA
         XYZ  3196    NA     NA

---

Edit: this works in MySQL as well, not sure why it did not work for you.

Docker setup:

r2@d2sb2:~$ docker pull mysql:latest
latest: Pulling from library/mysql
...
Digest: sha256:99e0989e7e3797cfbdb8d51a19d32c8d286dd8862794d01a547651a896bcf00c
Status: Downloaded newer image for mysql:latest
docker.io/library/mysql:latest

r2@d2sb2:~$ docker run --rm -p 3306:3306 --name some-mysql -e MYSQL_ROOT_PASSWORD=my-secret-pw -d mysql:latest
2e93acda94fdb386a9205ccda8df9b4f47b5f6a9bbdd159f84550418dff4a56e

Then in SQL:

SHOW VARIABLES LIKE 'version';
# Variable_name  Value
#       version 8.0.26

create schema stackoverflow;
use stackoverflow;
create table vendor (product_name text, product_value int, transaction_type text)
insert into vendor values ('ABC',3196,'sell'),('XYZ',3196,'sell'),('PQR',3196,'sell'),('ABC',2365,'buy'),('ABC',2365,'buy')

with tb1 as (
  select product_name, SUM(product_value) as sell
  from vendor
  where transaction_type='sell'
  group by product_name
),
tb2 as (
  select product_name, SUM(product_value) as buy
  from vendor
  where transaction_type='buy'
  group by product_name
)
select tb1.product_name, tb1.sell, tb2.buy, (tb2.buy - tb1.sell) as profit
from tb1 left join tb2 on tb1.product_name=tb2.product_name
# product_name sell  buy profit
#          ABC 3196 4730   1534
#          XYZ 3196   NA     NA
#          PQR 3196   NA     NA

(Note: I'm running the commands one-by-one in an R DBI client, not in the mysql console; I doubt it makes a difference other than R's NA as a null value. FYI.)

Since your instance seems to have some issue with the SUM without HAVING, this mod to the query produces the same results:

with tb1 as (
  select product_name, SUM(product_value) as sell
  from vendor
  group by product_name, transaction_type
  having transaction_type='sell'
),
tb2 as (
  select product_name, SUM(product_value) as buy
  from vendor
  group by product_name, transaction_type
  having transaction_type='buy'
)
select tb1.product_name, tb1.sell, tb2.buy, (tb2.buy - tb1.sell) as profit
from tb1 left join tb2 on tb1.product_name=tb2.product_name

Answer 2

Maybe you're after a simple conditional case to pivot your values, you can then simply subtract:

select productname, 
Coalesce(Sum(case when TransactionType='sell' then productvalue end),0) -
Coalesce(Sum(case when TransactionType='buy' then productvalue end),0) ProfitLoss
from t
group by productname

Example Fiddle