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