write a query to identify discrepancy

I have a table with Student ID's and Student Names. There has been issues with assigni开发者_如何学运维ng unique Student Id's to students and Hence I want to find the duplicates

Here is the sample Table:

Student ID  Student Name
   1                 Jack
   1                 John
   1                 Bill
   2                 Amanda
   2                 Molly
   3                 Ron
   4                 Matt
   5                 James
   6                 Kathy
   6                 Will

Here I want a third column "Duplicate_Count" to display count of duplicate records. For e.g. "Duplicate_Count" would display "3" for Student ID = 1 and so on. How can I do this?

Thanks in advance

  Select StudentId, Count(*) DupCount
  From Table
  Group By StudentId
  Having Count(*) > 1
  Order By Count(*) desc, 

Select
  aa.StudentId, aa.StudentName, bb.DupCount
from
  Table as aa
join
(
  Select StudentId, Count(*) as DupCount from Table group by StudentId
) as bb
on aa.StudentId = bb.StudentId

The virtual table gives the count for each StudentId, this is joined back to the original table to add the count to each student record.

If you want to add a column to the table to hold dupcount, this query can be used in an update statement to update that column in the table

This should work:

update mytable
set duplicate_count = (select count(*) from mytable t where t.id = mytable.id)

UPDATE:

As mentioned by @HansUp, adding a new column with the duplicate count probably doesn't make sense, but that really depends on what the OP originally thought of using it for. I'm leaving the answer in case it is of help for someone else.