CREATE TABLE #TESTING (acc_no varchar(20), number INT, c_name varchar(20), R_Value decimal(10,2), time_spent decimal(10,2) )

INSERT INTO #TESTING VALUES ('C1232' ,4445, 'Tom', 345.7, 43.56)
INSERT INTO #TESTING VALUES ('C1232' ,3456, 'Tom', 3454.7, 553.556)
INSERT INTO #TESTING VALUES ('C1232',6789, 'Thomas', 1345.7, 463.556)
INSERT INTO #TESTING VALUES ('C125632',1234, 'Will', 423.64, 233.77)
INSERT INTO #TESTING VALUES ('C125632',2345, 'William', 56.76, 77.89)
INSERT INTO #TESTING VALUES ('C125632',1345, 'Will', 444.56, 234.54)
INSERT INTO #TESTING VALUES ('C125632',12634, 'Will', 34.27, 112.56)


select * from #TESTING

CREATE TABLE #Result1 (acc_no varchar(20), number INT, c_name varchar(20), R_Value decimal(10,2), time_spent decimal(10,2) )

INSERT INTO #Result1 VALUES ('C1232' ,4445, 'Tom', 5146.1, 1060.672)
INSERT INTO #Result1 VALUES ('C125632' ,1234, 'Will', 959.23, 658.76)



select * from #Result1

; WITH MyCTE (acc_no, c_name, cnt)
AS
( SELECT acc_no
       , c_name
       , COUNT(c_name)
   FROM #testing a
   GROUP BY acc_no
          , c_name  
)
SELECT 
  t.acc_no
, c.c_name
, number_sum = SUM( t.number) 
, r_value_sum = SUM( t.R_Value) 
 FROM #TESTING t
   INNER JOIN mycte c
     ON t.acc_no = c.acc_no
 WHERE c.cnt = (SELECT MAX(d.cnt)
                 FROM MyCTE d
                 WHERE d.acc_no = c.acc_no
               )
 GROUP BY t.acc_no
        , c.c_name

;WITH SumPerAcct AS
	(SELECT acc_no, SUM(R_Value) AS Sum_R_Value, SUM(time_spent) AS Sum_time_spent
	 FROM #TESTING
	 GROUP BY acc_no),
	OccurrenceCount AS
	(SELECT acc_no, MAX(number) AS maxNumber, c_name, Count(c_name) NumerOfOccurence, ROW_NUMBER() OVER (PARTITION BY acc_no ORDER BY COUNT(c_name) DESC) Sequences
	 FROM #TESTING 
	 GROUP BY acc_no, c_name),
	MaxOccurrence AS
	(SELECT acc_no, c_name
	 FROM OccurrenceCount
	 WHERE Sequences = 1)
	SELECT SumPerAcct.acc_no,  OccurrenceCount.maxNumber, MaxOccurrence.c_name, SumPerAcct.Sum_R_Value, SumPerAcct.Sum_time_spent
	FROM SumPerAcct --ON SumPerAcct.acc_no = #TESTING.acc_no
	JOIN OccurrenceCount ON OccurrenceCount.acc_no = SumPerAcct.acc_no
	JOIN MaxOccurrence ON MaxOccurrence.acc_no = SumPerAcct.acc_no
		AND MaxOccurrence.c_name = OccurrenceCount.c_name

with cte1 as (
select acc_no,number,c_name,
       sum(R_Value) over(partition by acc_no) as R_Value,
       sum(time_spent) over(partition by acc_no) as time_spent,
       count(*) over(partition by acc_no,c_name) as cn
from #TESTING),
cte2 as (
select acc_no,number,c_name,R_Value,time_spent,
       row_number() over(partition by acc_no order by cn desc,number desc) as rn
from cte1)
select acc_no,number,c_name,R_Value,time_spent
from cte2
where rn=1