Compare two totals using LEFT JOIN in MySql

Question

Compare two totals using LEFT JOIN in MySql

Navigation

#1 by (0 votes)

0

I have two tables: Venda and ProdutosVenda , where the Venda table has a field named 'Co_Id' and the ProdutosVenda , which stores the products of that sale, has a field called 'Id_Reference', which is the reference of the sale in the products ('Id_Referencia' = 'Id_Chave').

It turns out that I'm trying to add the totals of these products for each sale (% with%) and compare it with the total of the sale itself (% with%) to look for inconsistencies in the table. But in the code I've done I'm having the problem of duplicating the result on the side of the ProdutosVenda table and this does not happen if I make two separate queries.

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

Result:

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 it different it doubled the value that would be expected from TotalVenda, which would be 2184.32. Thing that does not happen if I make the individual query for this table.

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

mysql sql

asked by anonymous 22.01.2018 / 14:13

1 answer

ng serves to load when it reaches 70% on Ubuntu Very crazy error in registry PostgreSQL X FastReport

score 0 · Accepted Answer

When there are 2 tables (1 for many), you have to start from the table with the most related records, group them and reference them 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 will be 1 per line): You will make a selet for sale and one for sum of items. Join the 2 tables in 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