Need MYSQL uquery solution

Question

I have the folowing tree tables one is the stock_items with all the items in it. The stock_in has the stock movements in to the stock and stock_out has the out movements:

and I want to get such a query result:

could some one help me to make this query?

Please post sample data, expected outcome and the code you have so far as text to the question and read https://stackoverflow.com/help/how-to-ask — P.Salmon, Dec 13 '21 at 16:01
Next time please don't post images. Paste the data as formatted text instead. Please read: [Please do not upload images of code/errors when asking a question.](//meta.stackoverflow.com/q/285551) — Thorsten Kettner, Dec 13 '21 at 18:00

Thorsten Kettner · Accepted Answer · 2021-12-13T17:57:32.260

You want to select the stock items and join the in totals and out totals. Aggregate to get the totals. Outer join to get items with and without transactions. Use COALESCE to replace nulls with zeros.

select
  s.barcode, s.item_name,
  coalesce(si.total, 0) as amount_in,
  coalesce(so.total, 0) as amount_out,
  coalesce(si.total, 0) - coalesce(so.total, 0) as balance,
  s.unit
from stock_items s
left join
(
  select barcode, sum(amount) as total
  from stock_in
  group by barcode
) si on si.barcode = s.barcode
left join
(
  select barcode, sum(amount) as total
  from stock_out
  group by barcode
) so on so.barcode = s.barcode
order by s.barcode;

score 0 · Answer 2 · answered Dec 13 '21 at 16:11

0

You can join tables using the barcode column?

select * from stock_items 
join stock_in on stock_items.barcode = stock_in.barcode
join stock_out on stock_items.barcode = stock_out.barcode

Just replace the * with a list of desired column names

answered Dec 13 '21 at 16:11

Sombriks

3,370
4
34
54

1

I wonder if stock_in and stock_out are summarised movements - op doesn't say.. – P.Salmon Dec 13 '21 at 16:15
I have made changes in op regarding to this question. We need to sume – Dec 13 '21 at 17:38

Matt Korostoff · Answer 3 · 2021-12-13T18:00:05.980

0

Unleash the power of subqueries and then use subtraction to compute the total stock level.

Basically the strategy here is to:

Run one query to sum up the total stock in
Run another query to sum up the total stock out
Run a final query subtracting the summed values.

select *, (stock_in - stock_out) as total from (select
    product_id pid,
    product_name,
    product_unit,
    (select sum(stock_in) from stock_in where product_id=pid) as stock_in,
    (select sum(stock_out) from stock_out where product_id=pid) as stock_out
from products) summed

SQL fiddle here: https://www.db-fiddle.com/f/v95qsALSfnm66HoQb6PqJ6/0

edited Dec 13 '21 at 18:00

answered Dec 13 '21 at 16:16

Matt Korostoff

1,927
2
21
26

This works perfect how you made it! I have forgotten to tell that in stock_in and stock out there could be more same product_id-s. What we need to sum some howe and show in the finale query the sum in and out and the diference of the two. – Dec 13 '21 at 17:27
I uploaded the new spreadsheet – Dec 13 '21 at 17:36
@TóthLászló I have updated my code and explanation to address this new information – Matt Korostoff Dec 13 '21 at 18:00

Need MYSQL uquery solution

3 Answers3