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

Generat Serial Number from Select Query T-SQL

There are times when you would want to generate your own serial numbers for a query. For eg: If we take the Customers table in Northwind database, the CustomerID is not numeric Eg: 'ALFKI'.

If you want to generate your own serial number for the Customers table while displaying the records, use the ROW_NUMBER(). As mentioned in BOL "ROW_NUMBER() returns the sequential number of a row within a partition of a result set, starting at 1 for the first row in each partition"

Here is a query that will help you generate your own serial numbers for the CustomerID

SELECT (ROW_NUMBER() OVER ( ORDER BY CustomerID)) AS ID ,
CustomerID, ContactName, CompanyName, ContactTitle
FROM Customers

ROW_Number() is a magical function. It can generate serials but it requires the column based on which it generate the Order. Also it can repeat the sequence on every occurrence of the given condition.
ROW_Number() require Minimum 1 and maximum 2 data to do all the magic.

1. Column to decide the order. Multiple column can be used in csv format
2. Column to decide Partition on which Row_Number() will regenerate serial from 1.

Lets see this in action

We have tables State(stid,stname) and city(ctid, ctname)

;WITH CTE(STID,STNAME, CTID, CTNAME,STIID)AS(
SELECT *  FROM TBSTATES S JOIN TBCITIES C ON S.ID=C.STATEID
)
 
SELECT STNAME, (ROW_NUMBER() OVER( PARTITION BY STNAME ORDER BY CTNAME))CITI_SNO, CTNAME FROM CTE

This will give a column citi_sno with repetition when state will change.

Fill up the data in table and try yourself.