title: SUM by two different GROUP BY
body: <p>I'm getting the wrong result from my report. Maybe i'm missing something simple.</p>
  
  <p>The report is an inline table-valued-function that should count goods movement in our shop and how often these spareparts are claimed(replaced in a repair). </p>
  
  <p>The problem: different spareparts in the shop-table(lets call it <code>SP</code>) can be linked to the same sparepart in the "repair-table"(<code>TSP</code>). I need the goods movement of every sparepart in <code>SP</code> and the claim-count of every <strong>distinct</strong> sparepart in <code>TSP</code>.</p>
  
  <p>This is a very simplified excerpt of the relevant part:</p>
  
  <pre><code>create table #tsp(id int, name varchar(20),claimed int);
  create table #sp(id int, name varchar(20),fiTsp int,ordered int);
  
  insert into #tsp values(1,'1235-6044',300);
  insert into #tsp values(2,'1234-5678',400);
  
  insert into #sp values(1,'1235-6044',1,30);
  insert into #sp values(2,'1235-6044',1,40);
  insert into #sp values(3,'1235-6044',1,50);
  insert into #sp values(4,'1234-5678',2,60);
  
  WITH cte AS(
      select tsp.id As TspID,tsp.name as TspName,tsp.claimed As Claimed
      ,sp.id As SpID,sp.name As SpName,sp.ordered As Ordered
      from #sp sp inner join #tsp tsp
      on sp.fiTsp=tsp.id
  )
  SELECT TspName, SUM(Claimed) As Claimed, Sum(Ordered) As Ordered
  FROM cte
  Group By TspName
  
  drop table #tsp;
  drop table #sp;
  </code></pre>
  
  <p>Result:</p>
  
  <pre><code>TspName       Claimed   Ordered
  1234-5678       400       60
  1235-6044       900       120
  </code></pre>
  
  <p>The <code>Ordered</code>-count is correct but the <code>Claimed</code>-count should be 300 instead of 900 for TspName='1235-6044'.</p>
  
  <p>I need to group by <code>Tsp.ID</code> for the claim-count and group by <code>Sp.ID</code> for the order-count. But how in one query?</p>
  
  <p><strong>Edit</strong>: Actually the TVF looks like(note that <code>getOrdered</code> and <code>getClaimed</code> are SVFs and that i'm grouping in the outer select on TSP's Category):</p>
  
  <pre><code>CREATE FUNCTION [Gambio].[rptReusedStatistics](
       @fromDate datetime
      ,@toDate datetime
      ,@fromInvoiceDate datetime
      ,@toInvoiceDate datetime
      ,@idClaimStatus varchar(50)
      ,@idSparePartCategories varchar(1000)
      ,@idSpareParts varchar(1000)
  )
  
  RETURNS TABLE AS
  RETURN(
      WITH ExclusionCat AS(
          SELECT idSparePartCategory AS ID From tabSparePartCategory
          WHERE idSparePartCategory IN(- 3, - 1, 6, 172,168)
      ), Report AS(
          SELECT Cat.SparePartCategoryName AS Category
          ,TSP.SparePartDescription AS Part
          ,TSP.SparePartName AS PartNumber
          ,SP.Inventory
          ,Gambio.getGoodsIn(SP.idSparePart,@FromDate,@ToDate) GoodsIn
          ,Gambio.getOrdered(SP.idSparePart,@FromDate,@ToDate) Ordered
          --,CASE WHEN TSP.idSparePart IS NULL THEN 0 ELSE
          --  Gambio.getClaimed(TSP.idSparePart,@FromInvoiceDate,@ToInvoiceDate,@idClaimStatus,NULL)END AS Claimed
          ,CASE WHEN TSP.idSparePart IS NULL THEN 0 ELSE
              Gambio.getClaimed(TSP.idSparePart,@FromInvoiceDate,@ToInvoiceDate,@idClaimStatus,1)END AS ClaimedReused
          ,CASE WHEN TSP.idSparePart IS NULL THEN 0 ELSE
              Gambio.getCostSaving(TSP.idSparePart,@FromInvoiceDate,@ToInvoiceDate,@idClaimStatus)END AS Costsaving
          FROM  Gambio.SparePart AS SP 
          INNER JOIN tabSparePart AS TSP ON SP.fiTabSparePart = TSP.idSparePart 
          INNER JOIN tabSparePartCategory AS Cat 
          ON Cat.idSparePartCategory=TSP.fiSparePartCategory
          WHERE Cat.idSparePartCategory NOT IN(SELECT ID FROM ExclusionCat)
          AND (@idSparePartCategories IS NULL 
              OR TSP.fiSparePartCategory IN(
                  SELECT Item From dbo.Split(@idSparePartCategories,',')
              )
          )
          AND (@idSpareParts IS NULL 
              OR TSP.idSparePart IN(
                  SELECT Item From dbo.Split(@idSpareParts,',')
              )
          )
      )
      SELECT Category
      --, Part
      --, PartNumber
      , SUM(Inventory)As InventoryCount
      , SUM(GoodsIn) As GoodsIn
      , SUM(Ordered) As Ordered
      --, SUM(Claimed) As Claimed
      , SUM(ClaimedReused)AS ClaimedReused
      , SUM(Costsaving) As Costsaving
      , Count(*) AS PartCount
      FROM Report
      GROUP BY Category
  )
  </code></pre>
  
  <hr>
  
  <p><strong>Solution</strong>:</p>
  
  <p>Thanks to Aliostad i've solved it by first grouping and then joining(actual TVF, reduced to a minimum):</p>
  
  <pre><code>WITH Report AS(
          SELECT Cat.SparePartCategoryName AS Category
          ,TSP.SparePartDescription AS Part
          ,TSP.SparePartName AS PartNumber
          ,SP.Inventory
          ,SP.GoodsIn
          ,SP.Ordered
          ,Gambio.getClaimed(TSP.idSparePart,@FromInvoiceDate,@ToInvoiceDate,@idClaimStatus,1) AS ClaimedReused
          ,Gambio.getCostSaving(TSP.idSparePart,@FromInvoiceDate,@ToInvoiceDate,@idClaimStatus) AS Costsaving
          FROM  (
              SELECT GSP.fiTabSparePart 
              ,SUM(GSP.Inventory)AS Inventory
              ,SUM(Gambio.getGoodsIn(GSP.idSparePart,@FromDate,@ToDate))AS GoodsIn
              ,SUM(Gambio.getOrdered(GSP.idSparePart,@FromDate,@ToDate))AS Ordered
              FROM Gambio.SparePart GSP
              GROUP BY GSP.fiTabSparePart
          )As SP
          INNER JOIN tabSparePart TSP ON  SP.fiTabSparePart = TSP.idSparePart
          INNER JOIN tabSparePartCategory AS Cat 
          ON Cat.idSparePartCategory=TSP.fiSparePartCategory
      )
      SELECT Category
      , SUM(Inventory)As InventoryCount
      , SUM(GoodsIn) As GoodsIn
      , SUM(Ordered) As Ordered
      , SUM(ClaimedReused)AS ClaimedReused
      , SUM(Costsaving) As Costsaving
      , Count(*) AS PartCount
      FROM Report
      GROUP BY Category
  </code></pre>