TroyGrosfield.com TroyGrosfield.com

Headline

Find All Non-Distinct Rows Using SQL

Author
by Troy Grosfield
Date
August 21st, 2010
Category
Developer
Story

I want to be able to find all non-distinct rows based on multiple columns, but not using all columns, and see how many occurrences there are. I would like to find all rows for people that have the same first_name, last_name, and middle_initial.

Data

Table Name: my_table

id first_name last_name middle_initial
1 Billy Bob J
2 Sam Smith Q
3 Billy Anderson S
4 Billy Bob J

SQL Statement

SELECT first_name, last_name, middle_initial, count(*) as num
FROM my_table
GROUP BY first_name, last_name, middle_initial
HAVING count(*) > 1;

Results

I should see the row that contains duplicates. So I would expect to see the following row:

first_name last_name middle_initial num
Billy Bob J 2

Billy Bob J is the only row that has multiple values for first_name, last_name, and middle_initial. Since there are 2 occurrences of this row, I would expect the num column to show 2.

Tags
Comments
No Comments »

No Comments Yet

Leave a reply