How to find most common words in a MySQL database and average a second column

mysql, php

Solution

You could do it like this:

SELECT
    AVG(t.Score) AS ScorceAvg,
    t.name
FROM
    (
        SELECT 
            SUBSTRING(Table1.Name,1,INSTR(Table1.Name, ' ')) AS name,
            Table1.Score
        FROM 
            Table1
        UNION ALL
        SELECT 
            SUBSTRING(Table1.Name,INSTR(Table1.Name, ' ')) AS name,
            Score
        FROM 
            Table1
    ) AS t
GROUP BY
    t.name

Problem

So I have two columns of text in a MySQL database, an example would be as follows: ``` Name Score Henry Hodgens 4 Mary Hodgens 8 Jim Servan 2 Jane Servan 4 Hank Servan 6 Sarah Smith 10 Mary Smith 12 Henry Dobbins 2 Henry Jenkins 4 ``` I need to run a query with PHP that can show the average of "Score", based on the most common occurrences of a single word in "Name". So, it would show that "Servan" averages 4, "Henry" averages 3.3, "Hodgens" averages 6, "Mary" averages 10, in the order of most occurrences of the word in "Name". I hope this makes sense.

Original source