CODEWITHSUNDEEP
databasesql-server-2008-r2normalization
Tyler DeWitt
Tyler DeWitt

Reputation: 23576

Clean up database records

I have a database that has no real normalization, so I am going back and trying to make it a little better. Right now we have the following columns:

FirstName | LastName | SchoolYear | Grade | SchoolId | Games Played
 Bob      | Smith    | 2010       |  Fr   | 245      | 3
 Tina     | Johnson  | 2010       |  So   | 2894     | 10
 Bob      | Smith    | 2010       |  Fr   | 245      | 3

How would I find schools that have 2 people with the same name, school year, grade, and school ID? They would look like duplicates in the database (all the columns would be the same), but each player only has one row with games played for each year. This is how I know there are similar people at a school, there would be 2 rows with the same information.

I'm using SQL Server 2008

Thanks!

Upvotes: 0

Views: 317

Answers (5)

Web User
Web User

Reputation: 7736

SELECT FirstName,
       LastName,
       SchoolYear,
       Grade,
       SchoolId,
       (CASE WHEN COUNT(*) > 1 THEN 'Yes' ELSE 'No' END) AS 'Repeat?'
FROM <table_name>
GROUP BY FirstName, LastName, SchoolYear, Grade, SchoolId

Upvotes: 1

Phil Klein
Phil Klein

Reputation: 7514

A simple GROUP BY and HAVING should do:

SELECT SchoolId
FROM YourTable
GROUP BY FirstName, LastName, SchoolYear, Grade, SchoolId, [Games Played]
HAVING COUNT(*) = 2

Note that you may want to use HAVING COUNT(*) > 1 if you want to find all schools with duplicate students.

Upvotes: 1

Seramme
Seramme

Reputation: 1350

If I understand your question correctly, this query:

SELECT FirstName, LastName, SchoolYear, Grade, SchoolId
FROM <your table>
GROUP BY FirstName, LastName, SchoolYear, Grade, SchoolId
HAVING COUNT(*) > 1

This will basically find all the (FirstName, LastName, SchoolYear, Grade, SchoolId) sets that exist in more than one record.

If you are only interested in the SchoolId's of the above records, then go with:

SELECT DISTINCT SchoolId
FROM <your table>
GROUP BY FirstName, LastName, SchoolYear, Grade, SchoolId
HAVING COUNT(*) > 1

Upvotes: 1

D&#39;Arcy Rittich
D&#39;Arcy Rittich

Reputation: 171491

select FirstName, LastName, SchoolYear, Grade, SchoolId
from Student
group by FirstName, LastName, SchoolYear, Grade, SchoolId
having count(*) > 1

Upvotes: 1

Randy
Randy

Reputation: 16677

select count(*), FirstName, LastName, SchoolYear, Grade, SchoolId
from mytable
group by FirstName, LastName, SchoolYear, Grade, SchoolId
order by 1 desc

Upvotes: 0

Related Questions