Calculate statistical mode in SQL Server 2005

Question

I have these 2 tables: Course(PK CourseId, Title) and Attendee(PK AttendeeId, FK CourseId, Method).
Many attendees can take a given course via any of 6 methods.

How can I print out one record for each course in the Course table (CourseId, Title) with the statistical mode of the Method for all the attendees to each course?

Answer 1

OK, from what I understand you need.

From this Mode (statistics),

The mode is not necessarily unique, since the same maximum frequency may be attained at different values.

So here goes

DECLARE @Course TABLE(
     CourseID INT,
     Title VARCHAR(50)
)

INSERT INTO @Course (CourseID,Title) SELECT 1, 'AA'
INSERT INTO @Course (CourseID,Title) SELECT 2, 'BB'
INSERT INTO @Course (CourseID,Title) SELECT 3, 'CC'

DECLARE @Attendee TABLE(
     AttendeeID INT,
     CourseID INT,
     Method INT
)

INSERT INTO @Attendee (AttendeeID,CourseID,Method) SELECT 1, 1, 1
INSERT INTO @Attendee (AttendeeID,CourseID,Method) SELECT 2, 1, 1
INSERT INTO @Attendee (AttendeeID,CourseID,Method) SELECT 3, 1, 2
INSERT INTO @Attendee (AttendeeID,CourseID,Method) SELECT 4, 1, 1
INSERT INTO @Attendee (AttendeeID,CourseID,Method) SELECT 5, 1, 3
INSERT INTO @Attendee (AttendeeID,CourseID,Method) SELECT 6, 1, 3
INSERT INTO @Attendee (AttendeeID,CourseID,Method) SELECT 7, 1, 4
INSERT INTO @Attendee (AttendeeID,CourseID,Method) SELECT 8, 1, 4
INSERT INTO @Attendee (AttendeeID,CourseID,Method) SELECT 9, 1, 5
INSERT INTO @Attendee (AttendeeID,CourseID,Method) SELECT 10, 1, 6
INSERT INTO @Attendee (AttendeeID,CourseID,Method) SELECT 11, 1, 6
INSERT INTO @Attendee (AttendeeID,CourseID,Method) SELECT 12, 1, 6

INSERT INTO @Attendee (AttendeeID,CourseID,Method) SELECT 13, 2, 1
INSERT INTO @Attendee (AttendeeID,CourseID,Method) SELECT 14, 2, 3
INSERT INTO @Attendee (AttendeeID,CourseID,Method) SELECT 15, 2, 3
INSERT INTO @Attendee (AttendeeID,CourseID,Method) SELECT 16, 2, 3
INSERT INTO @Attendee (AttendeeID,CourseID,Method) SELECT 17, 2, 3
INSERT INTO @Attendee (AttendeeID,CourseID,Method) SELECT 18, 2, 6

DECLARE @Counts TABLE(
     CourseID INT,
     Title VARCHAR(50),
     Method INT,
     NumberMethodPerCourse INT
)

INSERT INTO @Counts (CourseID,Title,Method,NumberMethodPerCourse)
SELECT  c.CourseID,
     c.Title,
     a.Method,
     COUNT(a.Method) NumberMethodPerCourse
FROM    @Course c INNER JOIN
     @Attendee a ON c.CourseID = a.CourseID
GROUP BY    c.CourseID,
      c.Title,
      a.Method

SELECT  CourseMax.CourseID,
     CourseMax.Title,
     CourseMax.MaxNumber,
     Counts.Method
FROM    (
      SELECT Counts.CourseID,
        Counts.Title,
        MAX(NumberMethodPerCourse) MaxNumber
      FROM @Counts Counts
      GROUP BY Counts.CourseID,
         Counts.Title
     ) CourseMax INNER JOIN
     @Counts Counts ON CourseMax.CourseID = Counts.CourseID
         AND CourseMax.MaxNumber = Counts.NumberMethodPerCourse