jeudi 23 juillet 2015

Joins with aggregates doubling, sometimes tripling quantity amounts

I'm trying to join 4 tables to get several columns of results, two of which are sums/aggregates of their respective columns. My query is returning multiples of what the true sums should be. Here is what I have:

select pl.[Vendor Item No_], bc.[Item No_], min(ile.[Description]) as 'Item Description',
        sum(ile.[Quantity]) as 'Quantity On Hand', 
        bc.[Bin Code] as 'Item Location'
 from [live$Bin Content]bc left outer join [live$purchase line]pl
  on bc.[Item No_] = pl.[No_]left outer join [live$item ledger entry] ile
  on bc.[Item No_] = ile.[Item No_]
where bc.[Bin Code] like 'ANNEX BACK'
and   bc.[Item No_] like 'sk%'


group by pl.[Vendor Item No_], bc.[Item No_], pl.[Description], bc.[Bin Code]

Aucun commentaire:

Enregistrer un commentaire