Mode average - need help for calculation in SQL Server

user_14926442 0
Viewed 54

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.

sql-server average sql
2 Answers

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

user_14106640

Could you please try something like:

WITH CTE(CLINIC,TEST) AS
(

  SELECT 'A123',2
    UNION ALL
  SELECT 'A123',3
   UNION ALL
  SELECT 'A123',    4
    UNION ALL
 SELECT 'A123' ,    3
   UNION ALL
 SELECT 'A123' ,    3
   UNION ALL
 SELECT 'B123' ,    2
   UNION ALL
 SELECT 'B123' ,    2
   UNION ALL
 SELECT 'B123' ,    2
   UNION ALL
 SELECT 'B123' ,    2
   UNION ALL
 SELECT 'B123' ,    2
  UNION ALL
 SELECT 'B123',     4
)
SELECT SQ.CLINIC,SQ.TEST   FROM 
(
   SELECT X.CLINIC,X.TEST,X.CNTT,ROW_NUMBER()OVER(PARTITION BY X.CLINIC ORDER BY    X.CNTT DESC)AS XCOL
  FROM
  (
   SELECT C.CLINIC,C.TEST,
   COUNT(*)CNTT
   FROM CTE AS C
   GROUP BY C.CLINIC,C.TEST
 )X 
)AS SQ WHERE SQ.XCOL=1;
user_145355170
Related