Group and Count Records in SQL Server

Group and Count Records in SQL Server: From SQL Blog

I had recently written a query to Select the Latest Record based on a Condition in SQL Server. A SQLServerCurry.com reader Mazz wrote back asking how to use the same query to count how many times a student took an exam for each course. Here’s the modified query that works on SQL Server 2005/2008.

SAMPLE DATA:

DECLARE @TT TABLE (CandID int, CourseID smallint)

INSERT INTO @TT VALUES (1, 101)
INSERT INTO @TT VALUES (3, 102)
INSERT INTO @TT VALUES (3, 110)
INSERT INTO @TT VALUES (3, 102)
INSERT INTO @TT VALUES (1, 101)
INSERT INTO @TT VALUES (2, 101)
INSERT INTO @TT VALUES (1, 110)
INSERT INTO @TT VALUES (1, 109)
INSERT INTO @TT VALUES (2, 102)
INSERT INTO @TT VALUES (1, 110)
INSERT INTO @TT VALUES (1, 101)
INSERT INTO @TT VALUES (3, 110)
INSERT INTO @TT VALUES (2, 102)
INSERT INTO @TT VALUES (2, 102)

QUERY

;WITH CTE
AS
(
SELECT
ROW_NUMBER() OVER (PARTITION BY CANDID, CourseID ORDER BY CandID) as ord,
CandID, CourseID
FROM @TT
)
SELECT CandID, CourseID, Count(CourseID) as TotalAttempts
FROM CTE
GROUP BY CandID, CourseID
ORDER BY CandID

OUTPUT