c# - Multi-column duplicates query -

c# - Multi-column duplicates query -

i'm using t-sql csharp query database table contains 5 columns of integers tracks number of times actions have taken place in program. there other columns in table.

example:

num1 num2 num3 num4 num5 1 15 22 23 32 15 4 21 17 19 6 5 15 18 20

i need build query returns each duplicated set of integers , count of rows of these 5 columns, when considered set of values, have been duplicated.

to clarify further, need know how many times num1=6, num2=5, num3=15, num4=18, , num5=20, if occur. need know if other sets of duplicates occur in these 5 columns.

i know sql, complex query need help with. i've tried many subqueries etc, can't figure out right combination of select , order by's create work. info table in question has 7000 records in , expected grow no larger 10k, performance secondary.

thanks in advance.

you can bring together on subquery, takes duplicates , count them

select a.col1, a.col2, a.col3, a.num1, a.num2, a.num3, a.num4, a.num5, t.cnt numberofduplicates tablea bring together (select num1, num2, num3, num4, num5, count(*) cnt tablea grouping num1, num2, num3, num4, num5 having count(*) > 1 ) t on a.num1 = t.num1 , a.num2 = t.num2 , a.num3 = t.num3 , a.num4 = t.num4 , a.num5 = t.num5

c# tsql sql-server-2008-r2