title: Distinct SUM with many-to-many relationships
body: <p>this is a followup question os <a href="https://stackoverflow.com/questions/9617616/how-to-combine-group-by-and-row-number">this</a>. Since the other answers an important part(how to <code>SUM</code> by partition) i keep it accepted. But another part worries me.</p>
  
  <p>How to <code>SUM</code> in a many-to-many relationship if the "left" side should counted just once.</p>
  
  <p>Consider following sample data which hopefully explains my problem:</p>
  
  <pre><code>declare @t1 table (ID int,Price money, Name varchar(10))
  declare @t2 table (ID int,Orders int,  Name varchar(10))
  declare @relation  table (t1ID int,t2ID int)
  insert into @t1 values(1, 200, 'AAA');
  insert into @t1 values(2, 150, 'BBB');
  insert into @t1 values(3, 100, 'CCC');
  insert into @t2 values(1,25,'aaa');
  insert into @t2 values(2,35,'bbb');
  insert into @relation values(1,1);
  insert into @relation values(2,1);
  insert into @relation values(3,2);
  -- following record will cause the "wrong" sum
  insert into @relation values(2,2);
  
  select
   T2.Name as T2Name
  ,T2.Orders As T2Orders
  ,T1Sum.Price As T1SumPrice
  ,T1.Price As T1HighestPrice
  ,T1.Name As T1HighestPrice_Name
  FROM @t2 T2
  INNER JOIN (
      SELECT Rel.t2ID
          ,Rel.t1ID
          ,ROW_NUMBER()OVER(Partition By Rel.t2ID Order By Price DESC)As PriceList
          ,SUM(Price)OVER(PARTITION BY Rel.t2ID) AS Price
          FROM @t1 T1 
          INNER JOIN @relation Rel ON Rel.t1ID=T1.ID
  )AS T1Sum ON  T1Sum.t2ID = T2.ID AND t1Sum.PriceList = 1
  INNER JOIN @t1 T1 ON T1Sum.t1ID=T1.ID
  </code></pre>
  
  <p>Actual result:</p>
  
  <pre><code>T2Name  T2Orders    T1SumPrice  T1HighestPrice  T1HighestPrice_Name
  aaa         25          350,00          200,00          AAA
  bbb         35          250,00          150,00          BBB
  </code></pre>
  
  <p>Desired result:</p>
  
  <pre><code>T2Name  T2Orders    T1SumPrice  T1HighestPrice  T1HighestPrice_Name
  aaa         25          350,00          200,00          AAA
  bbb         35          100,00          150,00          BBB
  </code></pre>
  
  <p>Notice the difference in <code>T1SumPrice</code>. The last entry in the relation-table inserts a T1 to <code>bbb</code> that was already assigned to another T2(<code>aaa</code>), hence it's already part of <code>T1SumPrice</code> for T2-group with Name=<code>aaa</code>. </p>
  
  <p>So how can i prevent counting values twice when they are already part of another group?</p>
  
  <p><strong>Edit</strong>:</p>
  
  <p>This is the resulting query from <a href="https://stackoverflow.com/a/9665749/284240">Nikola's answer</a>(which is actually part of a table-valued-function). Note that i need to use the scalar-valued-functions to retrieve the values:</p>
  
  <pre><code>select T2.idSparePart As T2_ID
  ,T2.PartNumber as T2_PartNumber
  ,Gambio.getGoodsIn(T2.idSparePart,@FromDate,@ToDate) AS GoodsIn
  ,Gambio.getOrdered(T2.idSparePart,@FromDate,@ToDate) AS Ordered
  ,T1Sum.ClaimedReused As T1SumPrice_ClaimedReused
  ,T1Sum.Costsaving As T1SumPrice_Costsaving
  ,T1.Price As T1HighestPrice
  ,T1.idSparePart AS T1HighestPrice_ID
  ,T1.SparePartName As T1HighestPrice_Number
  ,T1.SparePartDescription As T1HighestPrice_Name
  ,Cat.SparePartCategoryName As T1HighestPrice_Category
  FROM Gambio.SparePart T2
  INNER JOIN (
      SELECT Rel.fiSparePart
          ,Rel.fiTabSparePart
          ,ROW_NUMBER()OVER(Partition By Rel.fiSparePart Order By Price DESC)As PriceList
          ,SUM(Gambio.getMaterialQuantity(Rel.fiTabSparePart,NULL,NULL,@idClaimStatus,1))OVER(PARTITION BY Rel.fiSparePart) AS ClaimedReused
          ,SUM(Gambio.getCostSaving(Rel.fiTabSparePart,NULL,NULL,@idClaimStatus))OVER(PARTITION BY Rel.fiSparePart) AS Costsaving
          FROM tabSparePart T1 
          INNER JOIN 
            (select fiTabSparePart
              , fiSparePart
              from Gambio.trelSparePartClaimGroup relation
              where not exists (
                  select null 
                  from Gambio.trelSparePartClaimGroup rel 
                  where rel.fiTabSparePart = relation.fiTabSparePart
                  and rel.fiSparePart &lt; relation.fiSparePart
              )
          )
          Rel ON Rel.fiTabSparePart=T1.idSparePart
  )AS T1Sum ON  T1Sum.fiSparePart = T2.idSparePart AND t1Sum.PriceList = 1
  INNER JOIN tabSparePart T1 ON T1Sum.fiTabSparePart=T1.idSparePart
  INNER JOIN tabSparePartCategory AS Cat 
      ON Cat.idSparePartCategory=T1.fiSparePartCategory
  </code></pre>
  
  <p><strike>Unfortunately it seems to skip some T1-records so that i get a wrong result</strike></p>