Skip to content
Advertisement

Mode average – need help for calculation in SQL Server

I need to calculate the mode average by clinic. Test data as follows:

Clinic Test2
A123 2
A123 3
A123 4
A123 3
A123 3
B123 2
B123 2
B123 2
B123 2
B123 2
B123 4

I can show the mode for all clinics using

However, I want the mode per clinic, not all clinics. What I really want is to show:

Clinic Mode
A123 3
B123 2

Any help would be most appreciated. Thank you.

Advertisement

Answer

We can use window functions or grouping to count the occurrences of the Test2 value, then another window function (in a separate operation) to order the counts by most to least and choose only those most rows:

You can collapse it down a bit too, if you still find it readable/understandable:

If you had two Test2 with the same mode:

And you wanted them both, you can switch ROW_NUMBER to DENSE_RANK to return them both

User contributions licensed under: CC BY-SA
2 People found this is helpful
Advertisement