MySQL count row values WHERE column = value

Question

I have a database that looks pretty much like this:

I wanna make a MySQL query where I count the votes for each id and order them by starting from the highest. I want the output to be like:

Is it possible without making like 3 queries inside each other?

Solution

select
    name,
    sum(votes) as total_votes
from mytable
group by 1
order by 2 desc

Comments

[IMSoP]

Or, more readably, group by name and order by sum(votes). I've never been a fan of referencing columns by position.

[Cheezen]

Thanks a lot, worked like a charm! Btw IMSoP, shouldn't it be order by total_votes?

[Bohemian]

@IMSoP it's in the SQL standard, and I always use column position rather than expession in group/order by. The maintenance is way less using it, because the expression can be a) long, making the group by illegible and b) changed, meaning you have to copy-paste the new expression into both group and order by clauses - more hassle and frankly adding no value

[Bohemian]

@Cheezen yes: in mysql, you may refer to the column alias in order and group clauses

[IMSoP]

@Bohemian Matter of taste I guess. Column aliases, where allowed, I would definitely use. Column positions just seem a little hard to read - you have to find the Select clause and work out which column is being referenced; and if you added an extra column to your select, you could cause some very unexpected behaviour if you didn't update the references right.

[Bohemian]

@imsop but you've got to change the group by clause anyway when you change the select, no matter which style you use. The difference is do you add/change a digit, or copy-paste an expression/alias. To me the digit is simplest. And if you get the numbeds wrong, it will causes syntax error. If you omit a coln from group by, using numbers or names doesn't help one way or the other - forgetting a column is just as easy with one as the other.

[IMSoP]

@Bohemian That's true of Group By (assuming MySQL doesn't try to guess your intention), but not of Order By. Aliases, to me, are like variable names, and don't require copy-and-paste or much interpretation. Position numbers just seem kind of opaque; but like I say, matter of taste.