How to find products in stock?

Question

PostgreSQL

There are 3 tables

When a new product arrives, a new record is created in the receipt table with the indication good_id. If the goods arrived in a quantity of 4 pcs, there will be 4 new entries. When a product is sold, a new record Question
is created in the sale table .
How to find products in stock and with quantity in one query

Answer 1

select g.id, g.name, g.description, g.bar_code, s.count as total_sales, r.count as total_receipts, r.count-s.count as total  from good g join (select good_id, count(*) from receipt group by good_id) r on g.id = r.good_id
join (select good_id, count(*) from sale group by good_id) s on g.id = s.good_id