Query the amount of records that repeated in a table

[andreasblixt]

I'm spoiled with SQL Server 2005 and would do this:

WITH Temp AS (
SELECT
	CASE WHEN COUNT(SN) > 1 THEN 1 ELSE 0 END AS Repeating
FROM YourTable
GROUP BY SN
) SELECT SUM(Repeating) FROM Temp;

The closest equivalent query for that in SQL Server 2000 would be:

CREATE VIEW Temp AS
SELECT
	CASE WHEN COUNT(SN) > 1 THEN 1 ELSE 0 END AS Repeating
FROM YourTable
GROUP BY SN
GO

SELECT SUM(Repeating) FROM Temp
GO

DROP VIEW Temp
GO

There might be much better ways of doing this though.