Counting relations between two columns in SQL server

Question

Good morning, I have two columns in sql studio and I need to count the relations between the elements one column and the other one. The problem is it seems only is counting in 'one direction' and I want to know both. Maybe it is easier if I show you in a simple example. I am using SQL server.

This is the original table:

Col1    Col2
3       1
3       2
3       2
4       4
4       5
4       6
3       2
2       3
2       3

And if I do the following (based on count(concat)), it gives the following results.

select Col1, Col2, count(concat(Col1, Col2)) as weight 
 FROM test1
 group by Col1, Col2

Col1    Col2    weight
3       1       1
3       2       3
2       3       2
4       4       1
4       5       1
4       6       1

But what I would like is also counts the relations between the two elements, independently if this is in one column or other. So, the number of relations between 3-2 (or 2-3) would be 5. It is possible to do that?

3   2   5

Any suggestion would be very welcome! Thanks in advance!

score 2 · Accepted Answer · answered Apr 08 '19 at 14:22

Most databases support least() and greatest(). This allows you to do:

select least(col1, col2) as col1, greatest(col1, col2) as col2,
       count(*) as cnt
from t
group by least(col1, col2), greatest(col1, col2);

If your database does not support these handy functions, then you can implement similar logic using case expressions.

Counting relations between two columns in SQL server

1 Answers1