0
I have the following problem, I have two stocks in my company, so I have two values for the stock, and the stock record looks something like this:
as the picture shows, each product has two records, each one stating the quantity of each stock. I cannot change the way the record is made in the database, and I need an instruction that add the two, until then is not so difficult, what is making my hair fall is that I need this value to be informed in the same table of the code below
SELECT nome_produto AS Nome, Descricao_produto AS 'Marca/Laboratório', principio_ativo AS 'Principio ativo', produtos_precos.Custo_produto AS Custo, produtos_precos.Precovenda_produto AS 'Preço a vista', produtos_precos.precoprazo_produto AS 'Preço a prazo', SUM(produtos_estoque.Estoque_produto) AS Estoque
FROM genius.produtos
LEFT JOIN genius.produtos_precos ON produtos_precos.id_produto = produtos.id
LEFT JOIN genius.produtos_estoque ON produtos_estoque.id_produto = produtos.id
WHERE `nome_produto` LIKE '%ana%' AND genius.produtos_precos.id_empresa = 3;
without the part "SUM(products_stock.Stock_product) AS Stock" it returns to the following table:
but when I add the part of the sum it returns it to me:
that is, it sums up the stock of all products that match the survey, and does not give me the sum of each product
well, in short, I need a table exactly like the first picture, but with a column more "stock" that adds up the two stocks of each product
I already had this problem to put together price tables, where for each product there were 5 different prices and all were in the same table. What I did was put a LEFT JOIN PRODUCTS P2 ON P1.PROD_ID = P2.PROD_ID. In select pulled the price column for each PRODUCT TABLE referenced: SELECT P1.PRECO, P2.PRECO, ... In your case it would be the balance of each stock, a third column could be the sum of the stock balance 1 with the stock2. I think this can help you.
– Lindomar Lemos
@Rebertojunior, your question is not very expensive, I did not understand where you want to get it is to add all the calories or create a stock column (but with a column more "stock")
– Marco Souza