CODEWITHSUNDEEP
sqlsqlite
jamone
jamone

Reputation: 17421

sqlite get records with same column value

I have a SQLite DB that has people LastName, FirstName, Department and I need to make a query that shows me any people with the same First & Last Names. I've found the following statement that supposedly does what I want for a single field, however it doesn't seem to work for me when I try to use it to pull all records with just the last name being the same. How can I do this?

Select myField From myTable Group by myField Where count(myField)>1

Upvotes: 7

Views: 8499

Answers (2)

KM.
KM.

Reputation: 103589

try:

Select 
    firstname,LastName,Count(*)
    From myTable 
    Group by firstname,LastName 
    HAVING Count(*)>1

GROUP BY combines rows where the named values are the same.
HAVING removes groups that do not meet the condition.

The above query will list the first and last names, along with a count of duplicates for all first/last names that actually have duplicates.

Upvotes: 12

Larry Lustig
Larry Lustig

Reputation: 50970

Firstly, you need to use HAVING, not WHERE to qualify the GROUPed BY result:

 SELECT myField FROM myTable GROUP BY myField HAVING COUNT(myField) > 1

Secondly, you can extend this to multiple columns like this:

 SELECT MyCol1, MyCol2 FROM MyTable 
 GROUP BY MyCol1, MyCol2
 HAVING COUNT(*) > 1

Upvotes: 3

Related Questions