Getting Top 20 mysql Query Error

Trying to get: The top-20 PIs who have 开发者_运维百科the largest total amount of awards along with the universities they are affliated with.

Mysql: SELECT award, pi, org FROM tbl WHERE groupby(award) LIMIT 20

Table:

It seems to not like my group by. Whats going wrong here?

---

You need to group something, heres what your trying to do:

SELECT distinct award, pi, org FROM tbl LIMIT 20; 

---

You cannot include columns in a group by query that are not an aggregate column (i.e. the count) or not part of the grouping - this should work:

SELECT count(*) as ArwardCount, pi
FROM tbl 
GROUP BY pi
ORDER BY ArwardCount desc
LIMIT 0, 20;

---

With

SELECT award, pi, org FROM tbl WHERE groupby(award) LIMIT 20

You would get SQL Error (1305): FUNCTION xxx.groupby does not exist

You need the below instead, assuming pi alone is unique, and org is in the same table (denormalised)

SELECT pi, org, count(award) awardcount
FROM tbl
GROUP BY pi, org
ORDER BY awardcount DESC
LIMIT 20;