SELECT with LEFT JOIN successfully but some wrong results

Asked

Viewed 132 times

0

I need to make one SELECT using 2 tables with LEFT JOIN, I successfully get the query, but it brings me some columns with wrong results.

The results of the First table are:

tbEntregas

--Manupart----sum(Qtd)---avg(Custounit)-----sum(Custototal)-----Count(Id)
---RC 018----------258--------------18.768--------------4939.70---------------43-----

The results of the second table are:

tbCustosProd

--Manupart----sum(Qtd)----avg(Valorunit)-----sum(Total value)------Count(Id)
---RC 018----------164--------------10.214---------------1694.81---------------9------

The Query with LEFT JOIN brings the results where I make the use of SUM() in a strange way; it makes the Sum of all fields where the ManuPart are equal (so far all right), but it multiplies the result of the sum with the amount of records that exist of the same ManuPart from the other table.

Something like this:

 Select sum(Qtd) * 
 (select count(Id) from tbEntregas where ManuPart = 'RC 018')
 from tbCustosProd 
 where ManuPart = 'RC 018'

My complete Query is:

 select entregas.ManuPart, 
 sum(entregas.Qtd),
 avg(entregas.ValorUnit), 
 sum(entregas.ValorTotal),
 sum(custos.Qtd),
 avg(custos.CustoUnit),
 sum(custos.CustoTotal)
 from tbEntregas entregas 
 left join tbCustosProd custos 
 on entregas.ManuPart = custos.ManuPart 
 and entregas.Qtd > 0
 group by entregas.ManuPart;

These are the results I got:

deliveries.Manupart = RC 018 right.

Sum(deliverables.Qty) = 2322 wrong.

avg(deliveries.Valorunit) = 18.768 right.

sum(total value) = 44457.30 wrong.

sum(costs.Qty) = 7052 wrong.

avg(costs.Custounit) = 10.214 right.

sum(costs.Custototal) = 72876.83 wrong.

sql

  • Mauricio, can you make the create table of your tables available together with an Internet data? Just to recreate your example and try to replicate the "error", the way it is in the question, even editing it, is still a little confused.

  • Create the structure in http:/sqlfiddle.com/

  • @Marceloboni, CREATE TABLE tbEntregas ( Id int(11) NOT NULL AUTO_INCREMENT, ManuPart varchar(255) DEFAULT NULL, Qtd int(11) DEFAULT NULL, ValorUnit double(9,2) DEFAULT NULL, ValorTotal double(9,2) DEFAULT NULL, PRIMARY KEY (Id) ) ENGINE=Myisam AUTO_INCREMENT=9997 DEFAULT CHARSET=latin1;

  • @Marceloboni, CREATE TABLE tbCustosProd ( Id int(1) NOT NULL AUTO_INCREMENT, ManuPart varchar(255) DEFAULT NULL, Qtd int(11) DEFAULT NULL, CustoUnit double(9,2) DEFAULT NULL, CustoTotal double(9,2) DEFAULT NULL, PRIMARY KEY (Id) ) ENGINE=Myisam AUTO_INCREMENT=2758 DEFAULT CHARSET=latin1;

  • first I think the structure of your tables are confusing, for example.. why do you have a total value column? If you have the quantity and unit value, this column can be calculated and not inserted in a column.

  • (continuing) Second, what exactly is your tbCustosProd table? I understood that Voce has a table to record deliveries and another reference to know the unitary value of each product (tbCustosProd) so why do you have Custototal and quantity in tbCustosProd? Third, why don’t you use a number instead of text as a key? I created a Fiddle to make it easier for us http://sqlfiddle.com/#! 18/c59ee/3

Show 1 more comment

1 answer

0

create table #tbCustosProd
(
    Id int NOT NULL identity primary key, 
    ManuPart varchar(255) DEFAULT NULL, 
    Qtd int DEFAULT NULL, 
    CustoUnit decimal(9,2) DEFAULT NULL
)
GO

create table #tbEntregas
(
    Id int NOT NULL identity primary key, 
    ManuPart varchar(255) DEFAULT NULL, 
    Qtd int DEFAULT NULL
)
GO

insert into #tbCustosProd (ManuPart, Qtd, CustoUnit) values ('RC 017', 1, 4)
insert into #tbCustosProd (ManuPart, Qtd, CustoUnit) values ('RC 018', 1, 8)
insert into #tbCustosProd (ManuPart, Qtd, CustoUnit) values ('RC 019', 1, 12)
insert into #tbCustosProd (ManuPart, Qtd, CustoUnit) values ('RC 020', 1, 16)

insert into #tbEntregas (ManuPart, Qtd) values ('RC 017', 5)
insert into #tbEntregas (ManuPart, Qtd) values ('RC 017', 6)
insert into #tbEntregas (ManuPart, Qtd) values ('RC 018', 10)
insert into #tbEntregas (ManuPart, Qtd) values ('RC 018', 11)
insert into #tbEntregas (ManuPart, Qtd) values ('RC 019', 15)
insert into #tbEntregas (ManuPart, Qtd) values ('RC 019', 16)
insert into #tbEntregas (ManuPart, Qtd) values ('RC 020', 20)
insert into #tbEntregas (ManuPart, Qtd) values ('RC 020', 21)


select entregas.ManuPart, custos.custoUnit, sum(entregas.Qtd) totalQtd, (custos.custoUnit *  sum(entregas.Qtd)) totalVenda 
from #tbCustosProd custos
inner join #tbEntregas entregas on entregas.Manupart = custos.Manupart and entregas.Qtd > 0
group by entregas.ManuPart , custos.custoUnit

=> Sql Fiddle

Browser other questions tagged sql

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