using COUNT without GROUP BY | SQL Server Performance Forums

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
order by count(*) Results:
ssn dupes
111111111 4
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
Contributing Editor & Forums Moderator
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

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