You'll need to do the aggregation
before you join the tables.
DECLARE @tmp TABLE
(
ID INT,
[cprj] [varchar](500) NULL,
[TotalRowCount] [int] NULL,
[DWRowCount] [int] NULL,
[SumNoPartsPerLine] [int] NULL,
[CumuCount] [int] NULL
);
WITH ctePartList As
(
SELECT
cprj,
TotalRowCount,
DWRowCount,
Sum(NoPartsPerLine) As SumNoPartsPerLine
FROM
OrderEntry.dbo.OrderEntry
GROUP BY
cprj,
TotalRowCount,
DWRowCount
),
cteXmlLines As
(
SELECT
cprj,
Count(1) As CumuCount
FROM
SSIS.dbo.[MatchingRowsRUwithRU-DWCALCS-DATA-ProjectCumulative]
GROUP BY
cprj
)
INSERT INTO @tmp
(
cprj,
TotalRowCount,
DWRowCount,
SumNoPartsPerLine,
CumuCount
)
SELECT
P.cprj,
P.TotalRowCount,
P.DWRowCount,
P.SumNoPartsPerLine,
X.CumuCount
FROM
ctePartList As P
INNER JOIN cteXmlLines As X
ON X.cprj = P.cprj
;
SELECT * FROM @tmp;