CODEWITHSUNDEEP
sql-server-2008
user3166407
user3166407

Reputation: 65

sql query to get the count of duplicate rows

I have two tables say Students and Class

Student

St_ID int, St_Name nvarchar(100)

Class

Cl_ID int, Cl_Name nvarchar(100).

I want to get duplicate name of students (and count) from each class how can I write the query?

Upvotes: 0

Views: 4412

Answers (1)

Rumit Parakhiya
Rumit Parakhiya

Reputation: 2714

Assuming you have foreign key CL_ID from Class table in Student table, 

Select cl.CL_ID, st.St_Name, COUNT(st.St_ID)
From Student st
    Inner Join Class cl On st.CL_ID = cl.CL_ID
Group By cl.CL_ID, st.St_Name
Having COUNT(st.St_ID) > 1

Upvotes: 1

Related Questions