SQL Server: show string majority

I am looking to make a query in SQL SERVER that will allow me to display in a grouping the string that appears in most cases. Not the amount, not the maximum, but the string that is displayed in most cases:

colA	colB	colC
A	10	ccc
A	20	aaa
A	35	bbb
A	25	aaa
A	10	aaa
B	15	ccc
B	15	bbb
B	30	bbb

Select sum(colB) as total, ????????? as lable from table1 Group BY colA

Resukt:

colA	total	lable
A	100	aaa
B	60	bbb

thanks!

Answer

The simplest way is to calculate a ROW_NUMBER or a DENSE_RANK on the COUNT of records per ColA & ColC.

A DENSE_RANK = 1 could show more than 1 top if there’s a tie.

SELECT 
  ColA
, TotalColB AS Total
, ColC AS Lable
FROM
(
  SELECT ColA, ColC
  , TotalColB = SUM(SUM(ColB)) OVER (PARTITION BY ColA)
  , Rnk = DENSE_RANK() OVER (PARTITION BY ColA ORDER BY COUNT(*) DESC)
  FROM YourTable 
  GROUP BY ColA, ColC
) q
WHERE Rnk = 1
ORDER BY ColA;

ColA	Total	Lable
A	100	aaa
B	60	bbb

Test on db<>fiddle here