SQL Server Performance

using COUNT without GROUP BY

Discussion in 'T-SQL Performance Tuning for Developers' started by kev2971, Sep 19, 2006.

  1. kev2971 New Member

    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(*)

    ssn dupes
    111111111 4

    Thank you in advance and I appreciate the efforts here.


  2. Madhivanan Moderator

    What is your expected result?


    Failing to plan is Planning to fail
  3. kev2971 New Member

    Kudos on quick response!!!!!!

    I am looking for something like this.

    select name, account, ssn, current0 from master
    HAVING COUNT(ssn) > 1

    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!

  4. dineshasanka Moderator

  5. kev2971 New Member

    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!

  6. Twan New Member

    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 )


    select m1.* from master m1
    inner join ( select ssn from master group by ssn having count(*) > 1 ) m2
    where m1.ssn = m2.ssn

  7. kev2971 New Member

    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!

  8. Adriaan New Member

    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
  9. kev2971 New Member

    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!

  10. Twan New Member

    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


Share This Page