Finding Distinct Value using SQL

Show the titles and the award amounts of 20 awards that contain words discover, discoverer, discover开发者_JS百科y, discovered, and discovering in their abstracts.

My query:

SELECT title, count(award), abstract
FROM poop
ORDER BY award
WHERE abstract LIKE discov%
LIMIT 20

It doesnt like my where clause... Anyone? (In mySQL)

You are missing the single quotes around your criteria, you are only looking for words that start with "discov", your order by clause is in the wrong place, and you are using a count() expression without grouping by the other non-aggregate fields. In order to fix this and look for words containing "discov", use this:

SELECT title, abstract, count(award),  
FROM table 
WHERE abstract 
LIKE '%discov%' 
GROUP BY title, abstract
ORDER BY count(award) 
LIMIT 20

If you truly did mean to just look for words starting with "discov", then use this:

SELECT title, abstract, count(award),  
FROM table 
WHERE abstract 
LIKE 'discov%'
GROUP BY title, abstract
ORDER BY count(award) 
LIMIT 20

This query assumes distinct awards per pi.

SELECT COUNT(distinct award), pi 
FROM [tablename] 
GROUP BY pi 
ORDER BY COUNT(distinct award) DESC

States with top number of awards.

SELECT TOP 20 COUNT(distinct award), state
FROM [tablename]
GROUP BY state
ORDER BY COUNT(distinct award)

Remove the TOP 20 if you are not using T-SQL.

missing '' around discov and ORDER BY and WHERE are inversed:

SELECT title, count(award) AS count_award, abstract 
  FROM table 
 WHERE abstract LIKE 'discov%'
 GROUP BY title, abstract
 ORDER BY count_award DESC
LIMIT 20