1 Reply Latest reply on Nov 28, 2016 11:59 AM by Sarah Ebreo

Wrong SUM result when INNER JOIN multiplied row of records

BACKGROUND:

I map defects using background image and polygons.

For clarity, I am showing you the defect records found for one of the polygons

NC_KEYLEFT_RIGHT
LOCATION
Def_QTY
N1490364542LSECTION_431
N1490369230LSECTION_431
N1490329493LSECTION_431
N14903978680LSECTION_430
N14903750lLSECTION_431
N1490382545LSECTION_431
N1490393759LSECTION_431
N1490391497LSECTION_431
N1490405688LSECTION_431
N1490398211LSECTION_431
N1490378372LSECTION_431
N1490370496LSECTION_431
N1490396900LSECTION_431
N2780060835LSECTION_435

When I inner join these records with the table of Path ID, X, Y coordinates in order to plot the polygons, each defect record above is multiplied by the number of path ID - as expected by the INNER JOIN.

Here are the path IDs for one of the polygons in question

LEFT_RIGHTLOCATIONPath_IDX
Y
LSECTION_431230115
LSECTION_432655115
LSECTION_43365557
LSECTION_43462757
LSECTION_43560858
LSECTION_43658560
LSECTION_43725959
LSECTION_43822960

PROBLEM:

When I inner join the two data tables I get 112 rows (14 data rows x 8 path IDs). The expected defect qty for SECTION_43 polygon is 17 ( the sum of the Def Qty column).

But because of the multiplied number of records, I get 136. That is, each NC_KEY's quantity is "duplicated" 8 times and then summed up.

I tried Level of Detail (LOD) calculation and nested LOD but can't get the expected result.

Can you help please? Thank you.

• 1. Re: Wrong SUM result when INNER JOIN multiplied row of records

Since the records are being multiplied by the number of Path_IDs, you could try using the following formula in a calculated field:

SUM([Def QTY]) / COUNTD([Path ID])

Let me know if this helps!

Sarah