SUM by two different GROUP BY

Question

I'm getting the wrong result from my report. Maybe i'm missing something simple.

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).

The problem: different spareparts in the shop-table(lets call it SP) can be linked to the same sparepart in the "repair-table"(TSP). I need the goods movement of every sparepart in SP and the claim-count of every distinct sparepart in TSP.

This is a very simplified excerpt of the relevant part:

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;

Result:

TspName       Claimed   Ordered
1234-5678       400       60
1235-6044       900       120

The Ordered-count is correct but the Claimed-count should be 300 instead of 900 for TspName='1235-6044'.

I need to group by Tsp.ID for the claim-count and group by Sp.ID for the order-count. But how in one query?

Edit: Actually the TVF looks like(note that getOrdered and getClaimed are SVFs and that i'm grouping in the outer select on TSP's Category):

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
)

---

Solution:

Thanks to Aliostad i've solved it by first grouping and then joining(actual TVF, reduced to a minimum):

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

Answer 1

You are JOINing first and then GROUPing by. You need to reverse it, GROUP BY first and then JOIN.

So here in my subquery, I group by first and then join:

select 
    claimed,
    ordered 
from 
     #tsp 
inner JOIN
      (select 
             fitsp, 
             SUM(ordered) as ordered 
       from 
              #sp
        group by 
              fitsp) as SUMS           
on 
     SUMS.fiTsp = id;