Return average cost between partially equal products

Asked

Viewed 46 times

0

As exemplified in SQL Fiddle, given the tables, the data will be returned as follows:

nome     | descricao| tipo    | custo|    ult_lancamento      | entradas | saidas
---------------------------------------------------------------------------
ProdutoA | ProdutoA | ACABADO | 0.91 | May,12 2017 15:39:00   |   220    | 90
----------------------------------------------------------------------------
ProdutoB | ProdutoB | PRIMA   | 1.25 | March,24 2017 07:40:00 |   50     | (null)

That is, products that have the same name and the same description will be returned as a product containing an average cost, the sum of their entries and their exits and the last release date. In SQL Fiddle, it contains what I was able to do.

mysql sql join select-sql

1 answer

1


Follow code for your need: 

Select
p.nome,
p.tipo,
avg(p.custo) as custo_medio,
sum(coalesce(le.quantidade,0)) as entradas,
sum(coalesce(ls.quantidade,0)) as saidas,
(select MAX(data_lancamento) from lancamentos where id_produto=p.id) as ultimo_lancamento
from produtos p
left outer join lancamentos le on le.id_produto = p.id and le.operacao = 'ENTRADA'
left outer join lancamentos ls on ls.id_produto = p.id and ls.operacao = 'SAIDA'
where exists (select * from lancamentos where id_produto = p.id)
group by p.nome,p.tipo

Some Observations:

1-I do not understand why two products with the same name, it can bring you problems, do not do so.

2-Use the table of launches to store the cost price, this way you will have all the cost history of the product, in the respective date of its release. Same situation for sale price.

3-Do not use 'INPUT' and 'OUTPUT' to differentiate the records of the movement, when it is an output, use negative values, and input, positive, to extract the product balance, just run the 'Sum', and if it is still necessary to differentiate the records, use numbers, e.g.: 0-Input / 1-Output

  • his reply returned all products. Including those that had no launches. Also, the sum of the outputs was not made, note that the result was null. As for your comments, I am registering products that have the same name and description as there is the possibility of the same product being purchased from different suppliers and with different prices. As for the other two tips, I liked it.

  • on the first observation, the second solves the problem of suppliers and different prices, store the supplier also in the table of movement if it is the case, then you stay with the normalized bank, will have a history of purchases, prices and suppliers for each product. About leaving the products that had no movement, there was nothing specifying that they should not leave, so I opted for the left Outer Join, just replace this clause by Inner Join. =]

  • I returned to the Fiddle to see, only one product had 1 output, soon the others would leave as null same

  • @Ronvann switched to Ner Jack, but nothing was returned. In Fiddle Produtoa had a way out, but in the final sum (made with left Outer Join) left as null. As for your tips, I would keep the price and supplier in Launches and remove from the product, right?

  • I tried to open Fiddle to change the code and it gives sync error =/. And yes, in the same way that at launch you can have a customer to maintain the customer’s purchase history (Obs. I do customer and suppliers on the same table, only gets a FK in the table of releases)

  • You are really making a mistake. Do you mind explaining to me this idea of the fields through the Angouts chat? At least we do not fill the post here and when we solve the sql query we post the answer.

  • I have never used Angouts, just tell me how =] hehe, search for my name there that should appear

  • It’s gmail chat. I sent you the invitation

Show 3 more comments

Browser other questions tagged mysql sql join select-sql

You are not signed in. Login or sign up in order to post.