title: Summing Distinct Rows
body: <p>I am trying to get a total Sum per part, but for some reason it is not giving me a distinct sum with this code.  It is repeating the same part number based on number of records in the subquery.  What am I doing wrong?</p>
  
  <pre><code>Select Distinct IIF(x.omlPartID in ('10027','10028')
      ,x.omlPartID
      ,x.jmaPartID) as PartID
      , Sum(Cast(Total_Quantity as INT)) as SumTot
  From (
      SELECT distinct SalesOrderLines.omlsalesorderID
          , salesorderlines.omlSalesOrderLineID
          , SalesOrderLines.omlPartID
          , JobAssemblyMaterialCombo2 .jmaPartID
          ,  (
              IIF(rtrim(SalesOrderLines.omlPartID) in ('10027','10028')
              , 1
              , JobAssemblyMaterialCombo2.jmaQuantityPerParent
              ) * SalesOrderLines.omlOrderQuantity
              ) As 'Total_Quantity'
      FROM M1_HB.dbo.SalesOrderLines SalesOrderLines 
      Left Outer Join M1_HB.dbo.SalesOrderJobLinks SalesOrderJobLinks 
          On SalesOrderLines.omlSalesOrderID = SalesOrderJobLinks.omjSalesOrderID 
              AND SalesOrderlines.omlSalesOrderLineID = SalesOrderJobLinks.omjSalesOrderLineID 
      Left Outer Join M1_HB.dbo.Jobs Jobs 
          on SalesOrderJobLinks.omjJobID = Jobs.jmpJobID 
      Left Outer Join M1_HB.dbo.JobAssemblyMaterialCombo2 JobAssemblyMaterialCombo2 
          On Jobs.jmpJobID = JobAssemblyMaterialCombo2. jmaJobID 
      Left Outer Join M1_HB.dbo.SalesOrders SalesOrders 
          On SalesOrderLines.omlSalesOrderID = SalesOrders.ompSalesOrderID 
      Left Outer Join M1_HB.dbo.Organizations Organizations 
          On SalesOrders.ompCustomerOrganizationID = Organizations.cmoOrganizationID 
      WHERE JobAssemblyMaterialCombo2 .jmaPartID in ('10027','10028') 
          or (
              SalesOrderLines.omlPartID in ('10027','10028') 
              and 
              JobAssemblyMaterialCombo2 .jmaPartID is Null
              )
  ) x
  Group by x.omlPartID, x.jmaPartID
  </code></pre>