GROUP BY Question - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

Transact-SQL (2000)

GROUP BY Question

Author

Topic

dcarva
Posting Yak Master

140 Posts

Posted - 2004-04-19 : 11:29:02

Hello,

I searched for the forum for an answer, but didn't quite find what I was looking for. I have a table that has some duplicate machine names in it. I need to get a list of duplicates. The following:

SELECT [Name] AS "MachineName", COUNT( [Name] ) AS NumberofDuplicates
FROM SSIMachine
GROUP BY [Name]

...returns ALL rows GROUP and shows count. Ex:

Machine count
-------- -----
machine1 1
machine2 2
machine3 1

What I need is to only show duplicates, or where count > 1:

Machine count
-------- -----
machine2 2

So I tried this:

SELECT [Name] AS "MachineName", COUNT( [Name] ) AS NumberofDuplicates
FROM SSIMachine
WHERE NumberofDuplicates > 1
GROUP BY [Name]

That doesn't work. Any suggestions?
Thanks!

raymondpeacock
Constraint Violating Yak Guru

367 Posts

Posted - 2004-04-19 : 11:34:01

Try

SELECT [Name] AS "MachineName", COUNT( [Name] ) AS NumberofDuplicates
FROM SSIMachine
GROUP BY [Name]
HAVING COUNT(*) > 1

Raymond

dcarva
Posting Yak Master

140 Posts

Posted - 2004-04-19 : 12:04:10

That's it. Thank you!

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources