MySQL Select Statement - Two Tables, Sort One Table by Count of Other Table

Question

So I have built a voting system for a custom post system i wrote.

I want to be able to sort by "most voted", "Most liked", etc.

I have two tables.

Entry: ID, Title, Post

Vote: ID, EntryID, Result

I want to be able to query the vote table for each entry and see how many vote's there are, and then sort the entry's by how many vote's each table had. I have messed around with joins, etc. and cannot seem to figure it out. Any suggestions?

Answer 1

You want to make a join and group by the entry items, then use aggregates like count and sum to get the number of votes and the sum of votes:

select e.ID, e.Title, e.Post, count(*) as Votes, sum(Result) as Result
from Entry e
inner join Vote v on v.EntryId = e.Id
group by e.ID, e.Title, e.Post
order by 4 desc

Answer 2

how do we write same query using linq