Calculate statistical mode in SQL Server 2005

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?

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