T-SQL merging 2 rows into 1

Question

I have a table where I end up with two rows but I need a total of the data from both rows in one row, I can't use group by as I'm getting the usual must be in select list or contained in the group by clause.

DECLARE @Test TABLE 

(
FirstName NVARCHAR(10)
,Ref NVARCHAR (4)
,UserName NVARCHAR(30)
,File1 INT
,File2 INT
,ID INT
,Active Bit DEFAULT 0


)
INSERT INTO @Test VALUES ('John','AAAB','AAA Admin',5,10,677,1)
INSERT INTO @Test VALUES ('John (P)','AAAC','AAAC Admin',6,15,765,1)
INSERT INTO @Test VALUES ('John Admin','AAAG','AAA Admin',6,15,765,0)
INSERT INTO @Test VALUES ('Jane','AAUD','AAA Admin',6,15,765,0)
INSERT INTO @Test VALUES ('Jenny','AAOZ','AAA Admin',6,15,765,0)

;WITH CTE
 AS
 (
    SELECT *,ROW_NUMBER() OVER(Partition by FirstName Order by ID) RN
    from @Test
 )
 SELECT * FROM CTE
 WHERE RN=1 AND Active = 1
 ORDER BY ID DESC

[![Results][1]][1]

The File1 and File2 Values from INSERTS 3,4,5 are duplicate data and aren't required in the result.

Text :

FirstName   Ref UserName    File1   File2   ID  Active  RN
John (P)    AAAC    AAAC Admin  6   15  765 1   1
John    AAAB    AAA Admin   5   10  677 1   1

Expected Output:

Answer 1

I'm not sure what you want.

DECLARE @Test TABLE 
(
FirstName NVARCHAR(10)
,Ref NVARCHAR (4)
,UserName NVARCHAR(30)
,File1 INT
,File2 INT
,ID INT
,Active Bit DEFAULT 0
)
INSERT INTO @Test VALUES ('John','AAAB','AAA Admin',5,10,677,1)
INSERT INTO @Test VALUES ('John (P)','AAAC','AAAC Admin',6,15,765,1)
INSERT INTO @Test VALUES ('John Admin','AAAG','AAA Admin',6,15,765,0)
INSERT INTO @Test VALUES ('Jane','AAUD','AAA Admin',6,15,765,0)
INSERT INTO @Test VALUES ('Jenny','AAOZ','AAA Admin',6,15,765,0)

;WITH CTE
AS
(
    SELECT
        IIF(Tmp = 0, A.FirstName, SUBSTRING(A.FirstName, 0, A.Tmp)) FirstName,
        A.File1,
        A.File2
    FROM
    (
        SELECT 
            *,
            CHARINDEX(' ', FirstName, 0) Tmp
        FROM 
            @Test
        WHERE
            Active = 1
    ) A
)   

SELECT
    A.FirstName,
    SUM(A.File1) File1,
    SUM(A.File2) File2
FROM
    CTE A
GROUP BY    
    A.FirstName

Result:

FirstName   File1   File2
John        11      25