How to find duplicate records in SQL?

Question

I am trying to develop a query to insert unique records, but I am getting a SQL Server primary key error for trying to insert duplicate records. I managed to insert some values ​​with this query, but not for this record (score_14).

So now I am trying to find a duplicate entry with the following query. The challenge is that my PC is based on 3 columns: StudentID, MeasureDate and MeasureID - all from another table not mentioned below.

But it only shows me the score - instead, I just want to return the records with the score> 1. How do I do this?

select count(a.score_14) as score_count, A.studentid, A.measuredate, B.measurename+' ' +B.LabelName from [J5C_Measures_Sys] A join [J5C_ListBoxMeasures_Sys] B on A.MeasureID = B.MeasureID join sysobjects so on so.name = 'J5C_Measures_Sys' join syscolumns sc on so.id = sc.id join [J5C_MeasureNamesV2_Sys] v on v.Score_field_id = sc.name where so.type = 'u' and sc.name = 'score_14' and a.score_14 is not null AND A.STUDENTID IS NOT NULL AND A.MEASUREDATE IS NOT NULL AND B.MEASURENAME IS NOT NULL --and count(a.score_14)>1 group by a.studentid, a.measuredate, B.measurename, B.LabelName, A.score_14 having count(a.score_14) > 1 

Answer 1

Beth is right - here is my rewriting of your request:

 SELECT a.studentid, a.measuredate, a.measureid from [J5C_Measures_Sys] A GROUP BY a.studentid, a.measuredate, a.measureid HAVING COUNT(*) > 1 

Earlier:

 SELECT a.studentid, a.measuredate, a.measureid from [J5C_Measures_Sys] A join [J5C_ListBoxMeasures_Sys] B on A.MeasureID = B.MeasureID join sysobjects so on so.name = 'J5C_Measures_Sys' AND so.type = 'u' join syscolumns sc on so.id = sc.id and sc.name = 'score_14' join [J5C_MeasureNamesV2_Sys] v on v.Score_field_id = sc.name where a.score_14 is not null AND B.MEASURENAME IS NOT NULL GROUP BY a.studentid, a.measuredate, a.measureid HAVING COUNT(*) > 1 

Answer 2

you need to take A.score_14 from your group by clause if you want to read it