I have a user profile table with user id's and person's names. I won't to query the table to see if multiple users share the same user id. Ex: John Doe with User ID: 123; James Test with User ID: 123. Show me these occurrences grouped by User ID. Thanks!
Answer by Jeff Moden ·
Would the following do it for you? I ask because it only does one scan of the table instead of 3. It's what Thomas Rushton was suggesting.
SELECT User_ID, DupeCount = COUNT(*) FROM dbo.UserProfiles GROUP BY User_ID HAVING COUNT(*) >= 2 ;
Answer by ThomasRushton ·
This reads like homework, so I'm not going to give you an answer...
...but I suggest you look at the "HAVING" part of a "GROUP BY" clause.
If it's not homework, I suggest you then go and fix your table so there's a UNIQUE constraint on the USER ID field.