CODEWITHSUNDEEP
mysqlsql
Genadinik
Genadinik

Reputation: 18639

MySQL - how can I do a join and order by the sum of votes?

I have schema like this (just experimenting, so if you have improvement suggestions I am all ears):

mysql> describe contest_entries;
+---------------+----------------+------+-----+---------+----------------+
| Field         | Type           | Null | Key | Default | Extra          |
+---------------+----------------+------+-----+---------+----------------+
| entry_id      | int(10)        | NO   | PRI | NULL    | auto_increment |
| member_id     | int(10)        | YES  |     | NULL    |                |
| person_name   | varchar(10000) | NO   |     | NULL    |                |
| date          | date           | NO   |     | NULL    |                |
| platform      | varchar(30)    | YES  |     | NULL    |                |
| business_name | varchar(100)   | YES  |     | NULL    |                |
| url           | varchar(200)   | YES  |     | NULL    |                |
| business_desc | varchar(3000)  | YES  |     | NULL    |                |
| guid          | varchar(50)    | YES  | UNI | NULL    |                |
+---------------+----------------+------+-----+---------+----------------+
9 rows in set (0.00 sec)

mysql> describe contest_votes;
+------------------+---------+------+-----+---------+----------------+
| Field            | Type    | Null | Key | Default | Extra          |
+------------------+---------+------+-----+---------+----------------+
| vote_id          | int(10) | NO   | PRI | NULL    | auto_increment |
| user_id          | int(10) | NO   |     | NULL    |                |
| contest_entry_id | int(10) | NO   | MUL | NULL    |                |
| vote             | int(7)  | NO   |     | NULL    |                |
+------------------+---------+------+-----+---------+----------------+

And I am trying to pull the data as a leaderboard, ordering the results by the most votes. How would I do that? I am able to do the left-join part, but the sum and the ordering part of the query is confusing me.

Thank you!

Upvotes: 0

Views: 31

Answers (2)

Oswald
Oswald

Reputation: 31657

SELECT entry_id
FROM contest_entries
LEFT OUTER JOIN contest_votes ON entry_id = contest_entry_id
GROUP BY entry_id
ORDER BY SUM(vote) DESC

Upvotes: 1

juergen d
juergen d

Reputation: 204766

select e.entry_id, sum(v.vote) as votes
from contest_entries e
left join contest_votes v on e.entry_id = v.contest_entry_id 
group by e.member_id
order by votes desc

Upvotes: 1

Related Questions