SQL Server Count data in 2 columns with different values

Question

I want to do a smart count operation, so if the data in the columns is the same, then it will be considered as 1.

My table:

dbo.Messages 
(
    FromUserId INT,
    ToUserId INT
)

Data:

INSERT dbo.Messages VALUES(1, 5), (2, 20), (5, 1), (1, 5);

The count should return 2, because (1,5) and (5,1) are the same in my algorithm.

How can I write it in SQL Server TSQL?

Thanks in advance.

Answer 1

This works pretty well:

CREATE TABLE #Messages 
(
    FromUserId INT,
    ToUserId INT
);

INSERT #Messages VALUES(1, 5), (2, 20), (5, 1), (1, 5);

SELECT COUNT(*)
FROM (
  SELECT M1.FromUserId, M1.ToUserId
  FROM #Messages AS M1
  EXCEPT
  SELECT M2.ToUserId, M2.FromUserId
  FROM #Messages AS M2
  WHERE M2.ToUserId > M2.FromUserId
  ) AS T;

The view EXCEPTwill delete your duplicates, and then simply count the so-called unique values. Keep in mind that there is no need for a key DISTINCT, EXCEPTremoves all cheats.

View results:

FromUserId ToUserId 
---------- -------- 
1          5        
2          20   

, : https://data.stackexchange.com/stackoverflow/query/524634/counting-unique-values

Answer 2

- FromUserId ToUserId, . SQL Server, MySQL, LEAST GREATEST, CASE.

SELECT CASE WHEN t.FromUserId < t.ToUserId THEN t.FromUserId ELSE t.ToUserId END,
       CASE WHEN t.FromUserId < t.ToUserId THEN t.ToUserId   ELSE t.FromUserId END,
       COUNT(*) AS duplicateCount
FROM
(
    SELECT DISTINCT FromUserId, ToUserId
    FROM dbo.Messages
) t
GROUP BY CASE WHEN t.FromUserId < t.ToUserId THEN t.FromUserId ELSE t.ToUserId END,
         CASE WHEN t.FromUserId < t.ToUserId THEN t.ToUserId   ELSE t.FromUserId END

Answer 3

SQL Server 2008 :

SELECT distinct
    (SELECT Min(v) FROM (VALUES (FromUserId), (ToUserId)) AS value(v)) as UserIdMin,
    (SELECT Max(v) FROM (VALUES (FromUserId), (ToUserId)) AS value(v)) as UserIdMax
FROM dbo.Messages

: SQL MAX ?

Answer 4

select distinct  t1.*
from
#temp t1
join
#temp t2
on t1.FromUserId=t2.ToUserId
and t1.ToUserId=t2.FromUserId