Calculate the remaining stock of goods from two tables sql

Question

I Have 2 table
1.Table transaction in:

+-----+
|id_in|
+-----+
| 2   |
| 2   |
| 1   |
| 3   |
| 2   |
| 3   |
+-----+

2.Table transaction out:

+------+
|id_out|
+------+
|   2  |
|   2  |
|   3  |
|   1  |
+------+

I want to do a query so that it produces a difference between the two tables based on type where the amount of each is determined from the number of row count (*) so the result is

table result of query

+--+---------------+----------------+----------------+
|id|count row id_in|count row id_out|(rowin - rowout)|
+--+---------------+----------------+----------------+
|1 |      1        |      1         |     0          | 
|2 |      3        |      1         |     2          |  
|3 |      2        |      2         |     1          |  
+--+---------------+----------------+----------------+

How to query?

Answer 1

You can try below -

select idin, count_idin, count_idout,  count_idin-count_idout as result
from
(
  select idin,count(idin) count_idin from transactionin group by idin
)A inner join 
(
  select idout,count(idout) count_idout from transactionout group by idout
)B on A.idin=B.idout