SELECT with LEFT JOIN successfully but some wrong results

Question

SELECT with LEFT JOIN successfully but some wrong results

Navigation

#1 by (0 votes)

0

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

The results of the First table are:

tbUnderstand

- ManuPart ----- sum (Qtd) ---- avg (CustoUnit) ---- sum (TotalCal) ----- Count (Id)
--- RC 018 ---------- 258 -------------- 18,768 -------------- 4939.70 ---- ----------- 43 -----

The results of the second table are:

tbCustosProd

- ManuPart ----- sum (Qtd) ----- avg (ValueUnit) ---- sum (ValueTotal) ------- Count (Id)
--- RC 018 ---------- 164 -------------- 10,214 --------------- 1694.81 --- ------------ 9 ------

Query with LEFT JOIN brings the results where I make use of SUM() in a strange way; it does the sum of all fields where ManuPart are equal (so far everything is right), but it multiplies the result of the sum with the number of records that exist of the same ManuPart of 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;

Here are the results I got:

deliverables.ManuPart = RC 018 right .

Sum (renders.Qtd) = 2322 wrong .

avg (deliverables.ValueUnit) = 18.768 right .

sum (TotalTrends) = 44457.30 wrong .

sum (costs.Qtd) = 7052 wrong .

avg (costs.CustoUnit) = 10.214 right .

sum (costs.CountTotal) = 72876.83 wrong .

sql

asked by anonymous 06.03.2018 / 16:47

1 answer

Alert via jQuery not working as planned How to add two events in datepicker?

score 0 · Answer 1

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