Query the amount of records that repeated in a table

[sunny_sz]

Hi folks:

OS: Windows2000 Pro
DB: SQL 2000

Say: A table like below:

SN Status Datetime
001 pass 2007/11/05 01:30AM
002 fail 2007/11/05 01:50AM
002 pass 2007/11/05 02:00PM
003 fail 2007/11/05 02:30PM
003 fail 2007/11/05 03:00PM
003 fail 2007/11/05 03:15PM
004 pass 2007/11/05 03:30PM
.... ..... .........

Now what I want to get by using SQL is the amounts of records that repeated in the table above, the result that I want to get is: 2 , that is to say : there are two SNs that are repeated (002,003).

Could anybody please help me on how to achieve this??

Any help would be great appreciate !

Thanks in advance !

[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.

[sunny_sz]

Thanks , I will try .

[KrisSimonis]

It can be done a little easier yes, like this:



SELECT DISTINCT COUNT(SN)
FROM Table t1
WHERE (SELECT COUNT(*) FROM table t2 WHERE t2.SN = t1.SN) > 1

[ComITSolutions]

[QUOTE=sunny_sz]Hi folks:
Say: A table like below:

SN Status Datetime
001 pass 2007/11/05 01:30AM
002 fail 2007/11/05 01:50AM
002 pass 2007/11/05 02:00PM
003 fail 2007/11/05 02:30PM
003 fail 2007/11/05 03:00PM
003 fail 2007/11/05 03:15PM
004 pass 2007/11/05 03:30PM
.... ..... .........

select SN,Count(Sn) Cnt From ResultTable Group By Sn Having Count(sn)>1