Results 1 to 3 of 3
  1. #1
    Join Date
    Oct 2003
    Posts
    233

    Unanswered: Duplicate Records

    I have a db that doesnt have a pk, the field that should have been the pk now has duplicate records (so I am told)

    i wanted to get the records that had dups and how many so i tried :

    Select Count(*) as dups, shouldBePkColmn
    FROM myBadTable
    WHERE Count(*) >1
    Group by shouldBePkColmn

    but get the msg:
    An aggregate may not appear in the WHERE clause unless it is in a subquery contained in a HAVING clause or a select list, and the column being aggregated is an outer reference.


    Any help appreciated.

  2. #2
    Join Date
    Apr 2008
    Location
    Along the shores of Lake Michigan
    Posts
    242
    Try:

    Select shouldBePkColmn, Count(*) as dups
    FROM myBadTable
    WHERE Count(*) >1
    Group by shouldBePkColmn

    You're trying to determine dups in the group by so the column(s) you're grouping by must be in the order of you select.

  3. #3
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    correction:
    Code:
    SELECT shouldBePkColmn, COUNT(*) AS dups
      FROM myBadTable
    GROUP
        BY shouldBePkColmn 
    HAVING COUNT(*) > 1
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •