How to organize the search for duplicate values?

Question

SQL

It is necessary to determine whether the value will be repeated in other rows of the table. Wrote a query that looks for count(value) in one line.
I don't know if my code matters, without the database structure:

select count(DocumentNodeValue) as repeatitions, DocumentNodeValue, DocumentTemplateName, PatientName, d1.DocumentID, d2.DocumentID
from  
Patient 
join Course on PatientRef = PatientID
join Document d1 on CourseRef = CourseID
join Document d2 on d1.DocumentID = d2.DocumentID
join DocumentTemplate on d1.DocumentTemplateRef = DocumentTemplateID
join DocumentNode on DocumentRef = d1.DocumentID
group by DocumentNodeValue, DocumentTemplateName, PatientName, d1.DocumentID, d2.DocumentID
having count(DocumentNodeValue) > 1

I can not understand the logic of building the desired query, I need help.

Answer 1

Alternatively:
select distinct DocumentNodeValue, DocumentTemplateName, PatientName, d1.DocumentID, d2.DocumentID
from
(
select DocumentNodeValue, DocumentTemplateName, PatientName, d1.DocumentID, d2.DocumentID, count(*) over(partition by DocumentNodeValue) as repeatitions
from
Patient
join Course on PatientRef = PatientID
join Document d1 on CourseRef = CourseID
join Document d2 on d1.DocumentID = d2.DocumentID
join DocumentTemplate on d1.DocumentTemplateRef = DocumentTemplateID
join DocumentNode on DocumentRef = d1.DocumentID
) as tt
where tt.repeatitions > 1;
The logic is this: split the entire array of strings into groups by values ​​from the DocumentNodeValue. Count the number of rows in each group. Return all rows from the counted array. Take only rows that fall into groups in which more than 1 row is counted. + distinct is up to you.