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

SELECT TOP 1 test2, Clinic
  FROM [JFF].[dbo].[Test_table]
 GROUP BY clinic, test2
 ORDER BY COUNT(*) DESC

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.

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:

WITH x as (
  SELECT clinic, Test2, COUNT(*) as ct FROM [JFF].[dbo].[Test_table] GROUP BY clinic, test2
), y AS (
  SELECT x.*, row_number() over(PARTITION BY clinic order by ct desc) rn FROM x
)
SELECT clinic, test2 FROM y WHERE rn = 1

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

WITH x as (
  SELECT clinic, Test2, ROW_NUMBER() OVER(PARTITION BY clinic ORDER BY COUNT(*) DESC) as rn 
  FROM [JFF].[dbo].[Test_table] 
  GROUP BY clinic, test2
)
SELECT clinic, test2 FROM x WHERE rn = 1

If you had two Test2 with the same mode:

clinic test2
A123   2
A123   2
A123   5
A123   5

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