CODEWITHSUNDEEP
mysql
user2426701
user2426701

Reputation:

SELECT COUNT(*) for unique pairs of IDs

I have a table like the following, named matches:

match_id ( AUTO INCREMENT )
user_id ( INT 11 )
opponent_id ( INT 11 )
date ( TIMESTAMP )

What I have to do is to SELECT the count of the rows where user_id and opponent_id are a unique pair. The goal is to see the count of total matches started between different users.

So if we have:

user_id = 10 and opponent_id = 11
user_id = 20 and opponent_id = 22
user_id = 10 and opponent_id = 11
user_id = 11 and opponent_id = 10

The result of the query should be 2.

In fact we only have 2 matches that have been started by a couple of different users. Match 1 - 3 - 4 are the same matches, because played by the same couple of user IDs.

Can anyone help me with this?

I have done similar queries but never on pairs of IDs, always on a single ID.

Upvotes: 1

Views: 741

Answers (2)

Colin 't Hart
Colin 't Hart

Reputation: 7729

FancyPants answer is correct, but I prefer to use DISTINCT when no aggregate function is used:

SELECT COUNT(DISTINCT
  LEAST(user_id, opponent_id), 
  GREATEST(user_id, opponent_id)
)
FROM yourtable;

is sufficient.

Upvotes: 3

fancyPants
fancyPants

Reputation: 51928

SELECT COUNT(*) AS nr_of_matches FROM (
SELECT 
LEAST(user_id, opponent_id) AS pl1, 
GREATEST(user_id, opponent_id) AS pl2
FROM   yourtable
GROUP BY pl1, pl2
) sq

Upvotes: 1

Related Questions