Logo Questions Linux Laravel Mysql Ubuntu Git Menu
 

sqlite get records with same column value

Tags:

sql

sqlite

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 
like image 897
jamone Avatar asked Dec 23 '22 04:12

jamone


1 Answers

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.

like image 198
KM. Avatar answered Jan 02 '23 18:01

KM.