Compare two totals using LEFT JOIN in Mysql

Asked

Viewed 265 times

0

I have two tables: Venda and ProdutosVenda, where the table Venda has a field named 'Id_key' and the ProdutosVenda, that will store the products of this sale, has a field called 'Id_referencia', which is the reference of the sale in the products ('Id_referencia' = 'Id_chave').

I happen to be trying to sum up the totals of these products for each sale (ProdutosVenda) and compare with the total sale itself (Venda) to look for inconsistencies in the table. However in the code I did I am having the problem of it duplicate the result on the table side Venda and this does not happen if I make two separate consultations.

Follow my query:

SELECT ProdutosVenda.Id_Referencia, SUM(Venda.TotalVenda), 
SUM(ProdutosVenda.Subtotal), 
IF(TotalVenda = Subtotal, "Igual", "Diferente")
FROM Venda
LEFT JOIN ProdutosVenda ON Venda.Id_Chave = ProdutosVenda.Id_Referencia
WHERE Venda.DataOperacao = '2017-05-04'
GROUP BY ProdutosVenda.Id_Referencia

Upshot:

Id_Referencia | TotalVenda | Subtotal | Meu IF

00000001.1    | 3400       | 3400     | Igual
00000002.1    | 3399.9     | 3399.9   | Igual
00000003.1    | 4368.64    | 2184.32  | Diferente ***

It turns out that in the result that gave different he doubled the value that would be expected of Totalvenda, that would be 2184.32. Thing that doesn’t happen if I do the individual query for this table.

Obs.: This sale has two products, of 1092.16 each and the other sales that doubled also have more than one product.The ones that gave equal in the consultation have only one product.

mysql sql database

  • It’s probably happening because you have mais de 1 item em algumas vendas, then the value of tabela Venda repeats as the number of records of the tabela ProdutosVenda. What you could do is make a select with GROUP BY or DISTINCT summing up the items, and a select also with GROUP BY or DISTINCT in the sales table, and make a UNION in both, so would have the values 1 para 1.

  • I tried to do like you told me but it seems that only returns me the result of the first select and I need to appear the two results on the screen to check if there is a difference.

  • I will post an example in the answer.

1 answer

0


When there are 2 tables (1 for many), you have to start from the table that has more related records, group them and reference with the single record table.

Select:

SELECT Id_Chave, TotalVenda, SUM(Subtotal) ValorProdutos, Id_Referencia
FROM ProdutosVenda PV
LEFT OUTER JOIN Vendas VV ON PV.Id_Referencia = VV.Id_Chave
GROUP BY Id_Chave, Id_Referencia, TotalVenda

Select 2 (this way is 1 per line): You will make a selet for sale and one for sum of items. Merge the 2 tables into 1 only, sort by ID.

SELECT * FROM (
(SELECT ID, VALOR, NULL, NULL FROM VENDAS)
UNION ALL
(SELECT NULL, NULL, ID_VENDA, SUM(VALOR) FROM PRODUTOSVENDAS
GROUP BY ID_VENDA)
) T_ALIAS
ORDER BY ID

  • Returned an error: Duplicate column name 'NULL'

  • Glue your entire select here

  • SELECT * FROM ( (SELECT Id_chave, Totalvenda, NULL, NULL FROM Vendas) UNION ALL (SELECT NULL, NULL, Id_referencia, SUM(Subtotal) FROM Productsproducting GROUP BY Id_referencia) ) T_ALIAS ORDER BY Id_referencia

  • Test like this: SELECT * FROM ( (SELECT Id_key, Totalvenda, NULL Id_referencia, NULL Products FROM Sales) UNION ALL (SELECT NULL Id_chave, NULL Totalvenda, Id_referencia, SUM(Subtotal) Products FROM Productsproasing GROUP BY Id_referencia) ) T_ALIAS ORDER BY Id_reference

  • I’ll do another one with Join online

  • They even appeared but the second select only started when the records of the first.

  • I already edited my answer, and posted above. SAVED some error with relation table x your field. But the concept is there.

  • I got the online select that you sent me, but we’re back to square one, because the Totalvenda records add up to duplicates when there’s more than one product on sale.

  • Do you have skype ? Pass me if you have.

  • Yes, I do: victorairesmm

  • I’ll add it to you. But man, the first select there, it was to have worked out if you really have all the records of Products Selling, with a Sale, and vice versa.

  • Man, I figured it out. The mistake was mine, because the Totalvenda field is the total value of the sale, so there is no sum in it, only in the Subtotal field of Items. I did the correction and it worked right with your SQL. Thank you so much for the help. Hugging!

  • Oops, I just updated. Needed to take the SUM of Totalvenda, put in GROUP BY if it does not give error. If everything is ok. Mark as resolved. Hug.

Show 8 more comments

Browser other questions tagged mysql sql database

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