Joins with aggregates doubling, sometimes tripling quantity amounts

Question

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]

you're getting duplicates because the joins are one to many relationships which is causing the numbers to double/triple. You have to generate the aggregate before the joins via inline view/sub query or by use of an analytic. — xQbert, Jul 23 '15 at 20:14
I'm just not sure what the syntax is so that the item No's keep their relationship between the main query and the subquery... The item No is the common thread between all these tables... — Craig Zirnheld, Jul 23 '15 at 20:17

xQbert · Accepted Answer · 2015-07-23T20:39:20.600

using a subquery/inline view may solve your problem. assuming everything else is working. To know for certain we would need to know the PK/FK relationship between all 3 tables.

SELECT pl.[Vendor Item No_], 
       bc.[Item No_], 
       min(ile.[Description]) as 'Item Description',
       ile.[Quantity] as 'Quantity On Hand', 
       bc.[Bin Code] as 'Item Location'
 FROM [live$Bin Content] bc 
 LEFT JOIN [live$purchase line] pl
  on bc.[Item No_] = pl.[No_]
 LEFT JOIN  (SELECT sum(quantity) as Quantity, [Item no_] 
             FROM [live$item ledger entry] 
             GROUP BY [Item no_]) 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]

Per comment... if you want to add another table and aggregrate quantity...

   SELECT pl.[Vendor Item No_], 
           bc.[Item No_], 
           min(ile.[Description]) as 'Item Description',
           ile.[Quantity] as 'Quantity On Hand', 
           bc.[Bin Code] as 'Item Location'
     FROM [live$Bin Content] bc 
     LEFT JOIN [live$purchase line] pl
      on bc.[Item No_] = pl.[No_]
     LEFT JOIN  (SELECT sum(quantity) as Quantity, [Item no_] 
                 FROM [live$item ledger entry] 
                 GROUP BY [Item no_]) ile
      on bc.[Item No_] = ile.[Item No_]
     LEFT JOIN  (SELECT sum(NEWFIELD) as Quantity, [Item no_] 
                 FROM [newTable] 
                 GROUP BY [Item no_]) newAlias
      on bc.[Item No_] = newAlias.[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]

However if the field is in one of the existing tables, you just need to add it as a new field on the subquery...

SELECT pl.[Vendor Item No_], 
       bc.[Item No_], 
       min(ile.[Description]) as 'Item Description',
       ile.[Quantity] as 'Quantity On Hand', 
       bc.[Bin Code] as 'Item Location',
       ile.count as count of items with inventory in ILE.
 FROM [live$Bin Content] bc 
 LEFT JOIN [live$purchase line] pl
  on bc.[Item No_] = pl.[No_]
 LEFT JOIN  (SELECT sum(quantity) as Quantity, count(Quantity) as count, [Item no_] 
             FROM [live$item ledger entry] 
             GROUP BY [Item no_]) 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]

xQbert. This works, but if I want to sum() another table joined on the Item No would I just add this? and where would I add that?: LEFT JOIN (SELECT sum(quantity) as Quantity, [Item no_] FROM [live$SALES LINE] GROUP BY [Item no_]) ile — Craig Zirnheld, Jul 23 '15 at 20:30
you would add similar the field and table name would change but the select and join style would remain the same — xQbert, Jul 23 '15 at 20:37

Joins with aggregates doubling, sometimes tripling quantity amounts

1 Answers1