SQL Server: show string majority

user_17019046 0
Viewed 79

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!

group select sql-server sql string
2 Answers

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

user_40034190

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

user_36116690
Related