CODEWITHSUNDEEP
mysql
Jshee
Jshee

Reputation: 2686

Get top 20 results for users in mySQL

How to get: The top 20 universities in terms of the total amount of awards they received? enter image description here I've tried and it only gives me 1 record??

SELECT sum(amount), org
FROM iis
ORDER BY award
LIMIT 0, 20;

Upvotes: 0

Views: 250

Answers (3)

Jimmy Sawczuk
Jimmy Sawczuk

Reputation: 13614

Your issue is that you're asking for the sum of ALL universities. You need to use a GROUP BY clause to specify that you want it per university:

SELECT SUM(amount), org
FROM iis
GROUP BY org
ORDER BY SUM(amount) DESC
LIMIT 0, 20;

Upvotes: 1

BrokenGlass
BrokenGlass

Reputation: 160902

You need a grouping, otherwise you will only get one result, also you want to order by an aggregate column (sum of all amounts for a given organization) so you have to give it a name:

SELECT sum(amount) as total, org
FROM iis
GROUP BY org
ORDER BY total desc
LIMIT 0, 20;

Upvotes: 0

Jude Cooray
Jude Cooray

Reputation: 19862

What you are looking for is ORDER BY award DESC with a GROUP BY

The default sort order is ascending, with smallest values first. To sort in reverse (descending) order, add the DESC keyword to the name of the column you are sorting by

http://dev.mysql.com/doc/refman/5.0/en/sorting-rows.html

http://dev.mysql.com/doc/refman/5.0/en/group-by-functions.html

Upvotes: 0

Related Questions