-
November 20th, 2007, 11:20 PM
#1
Query the amount of records that repeated in a table
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 !
Little by little one goes far
Keep moving.......!
Nothing is impossible !
-
November 21st, 2007, 03:17 AM
#2
Re: Query the amount of records that repeated in a table
I'm spoiled with SQL Server 2005 and would do this:
Code:
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:
Code:
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.
-
November 21st, 2007, 04:15 AM
#3
Re: Query the amount of records that repeated in a table
Little by little one goes far
Keep moving.......!
Nothing is impossible !
-
November 22nd, 2007, 03:17 AM
#4
Re: Query the amount of records that repeated in a table
It can be done a little easier yes, like this:
Code:
SELECT DISTINCT COUNT(SN)
FROM Table t1
WHERE (SELECT COUNT(*) FROM table t2 WHERE t2.SN = t1.SN) > 1
On Error Kill(User)
-
February 24th, 2008, 02:56 AM
#5
Re: Query the amount of records that repeated in a table
[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
Last edited by ComITSolutions; February 25th, 2008 at 01:38 AM.
Encourage the efforts of fellow members by rating
Lets not Spoon Feed and create pool of lazy programmers
- ComIT Solutions
Posting Permissions
- You may not post new threads
- You may not post replies
- You may not post attachments
- You may not edit your posts
-
Forum Rules
|
Click Here to Expand Forum to Full Width
|