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:

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

Resukt:

thanks!

CodePudding user response：

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

CodePudding user response：

Here is another way to do it

select t.ColA,
       sum(t.total) as ColB,
       max(s2.ColC)
from   ( select s.ColA,
                s.ColC,
                sum(ColB) as total,
                count(1) as numbers
         from   strings s
         group by s.ColA, s.ColC
       ) t  
  outer apply ( select top 1 
                       s.ColA,
                       s.ColC,
                       sum(ColB) as total,
                       count(1) as numbers
                from   strings s
                where  s.ColA = t.ColA 
                group by s.ColA, s.ColC
                order by 4 desc
            ) s2
group by t.ColA

Try it in this DBFiddle