SQL Server Performance Forum – Threads Archive

using COUNT without GROUP BY

Is it possible to use COUNT without GROUP BY ?
I need to find duplicates based on one field but has info in other fields that varies. SELECT ssn,count(*)as dupes
FROM master
GROUP BY ssn
HAVING COUNT(*) > 1
order by count(*) Results:
ssn dupes
——————–
111111111 4
2222222224
3333333334
4444444445
5555555555
6666666666
7777777776 Thank you in advance and I appreciate the efforts here. KevinU

What is your expected result? Madhivanan Failing to plan is Planning to fail

Kudos on quick response!!!!!! I am looking for something like this. select name, account, ssn, current0 from master
HAVING COUNT(ssn) > 1 ssn________name________account________current0
—————————————————————-
123456789______Madhivanan, M____BR459________1000.00
123456789______BLOW, JOE________OU812________-812.00
123456789______DOE, JANE________666__________666.66
123456789______DOE, JOHN________867-5309_____86753.09 however I know in this form you get error due to no GROUP BY function. Thank you! Kev2971

There are ways that you can deduplicate your data http://www.sqlservercentral.com/columnists/ccubley/findinganddeletingduplicatedata.asp
http://www.sqlservercentral.com/columnists/chawkins/dedupingdatainsqlserver2005.asp
—————————————-
Contributing Editor & Forums Moderator
http://www.SQL-Server-Performance.Com

Thanks anyway dineshasanka but I am not trying to dedupe, I want to find the dupes and see all the other info but cant due to the required GROUP BY function. Appreciate the response though! Kev2971

Hi ya, you’d need to do a self join or subselect select * from master
where ssn in ( select ssn from master group by ssn having count(*) > 1 )
alternatively select m1.* from master m1
inner join ( select ssn from master group by ssn having count(*) > 1 ) m2
where m1.ssn = m2.ssn Cheers
Twan

Can’t see the forest with all these damn trees here!!! Geez, amazing how the simple things still work in a detailed world! Twan, thank you very much! I sincerely appreciate your help. This forum rocks! Kev2971

What’s wron with a plain old GROUP BY clause? You don’t have to put COUNT(*) in the column list, you can filter in the HAVING clause: select name, account, ssn, current0 from master
group by name, account, ssn, current0
HAVING COUNT(ssn) > 1

True, but I have to address everything from the select statement into the group by clause which kind of gets repetitious. Also, for whatever reason, that statement only located 3650 records in my database. Twans found 163336. It does work and I appreciate the response, its just I hate group by and it doesnt suit what I am looking for. Thank you for the info though Adriaan! Kev2971

The problem with group by is that you can either group by the columns or select them, you can’t group by a subset of the columns and select some additional ones so if you want to find all records that have duplicate ssn numbers in the table, but then for each of the rows with those ssn numbers get some other column info, then you can’t do that without a subselect or a self-join Cheers
Twan

]]>