lundi 20 avril 2015

SQL - count rows where one column matches another (pairs)

I have a voting system on a website where every user can vote for a pair of users (female and male) to win.

The votes are stored in a table like the one below.

voter_id |  voted_for | cell
90       |  30        | f
90       |  68        | m
42       |  30        | f
42       |  68        | m
111      |  74        | f
111      |  20        | m
45       |  120       | f
16       |  90        | f
16       |  135       | m
122      |  30        | f
122      |  68        | m
45       |  116       | m
46       |  30        | m
46       |  121       | f

Now, I want to count how many users voted for every pair. E.g. '3 votes for pair 30&68', '1 vote for pair 74&20', '1 vote for 120&116' and so on...

I have a SQL query which counts every user mentioned in voted_for but this doesn't include the pair-thing. It would give me 4 votes for user 30 but user 30 although is in the 30&121 pair.

SELECT voted_for, count(*) AS count
FROM votes
GROUP BY voted_for
ORDER BY count desc

Aucun commentaire:

Enregistrer un commentaire