title: Scoring Word Frequency in a MySQL Query
body: <p>I'm working with a one-table database that is composed of 2 columns: an integer wordID, and the varchar word. The table is several thousand lines long and was created by reading a lot of text in programatically and splitting on a space, then ucasing the individual words and inserting them into a database. The goal is to use this dictionary in order to read full-text blog posts, tweets, other text content and score them for relevance.</p>
  
  <p>What I'd like to do is compute the count of each word (which I have working by itself) as well as the "score" of each word - that is to say, a word mush show up a minimum number of times in the data set to have a score, and the score is the inverse of the frequency of the word, on a scale of 1-10. The thought is that the more often a word comes up, the less valuable it'll be in my text-search later down the line. However it must appear a minimum number of times in order to be useful as well, because a one-off probably was a typo.</p>
  
  <p>Here's my select statement and attempt to score word frequencies at the same time as generating a count.</p>
  
  <pre><code>  select word, 
    count(word), 
    10*(((max(count(word))+1) - count(word))/(max(count(word))))
    from dictwords where length(word)&gt;3 group by word having count(word)&gt;35 
    order by count(word) desc;
  </code></pre>
  
  <p>The error returned by mysql is "Invalid use of group function." Error 1111.</p>
  
  <p>Is it possible to do this sort of thing in one statement in mySQL? Or should I break up the counting and scoring into two queries by doing a select into and feed my result table into a place holder table then attempt to score it?</p>