CODEWITHSUNDEEP
phpmysql
Jason Ehmke
Jason Ehmke

Reputation: 125

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

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.

Upvotes: 4

Views: 4438

Answers (4)

Muhammad Raheel
Muhammad Raheel

Reputation: 19882

Here is th perfect thing 

SELECT distinct
       SUBSTRING(NAME, LOCATE(' ',NAME)+1) as NameED,
      (select
       avg(score)
       from avgscore
       where NameED = SUBSTRING(NAME, LOCATE(' ',NAME)+1)) as Score
FROM avgscore

Upvotes: 1

Arion
Arion

Reputation: 31239

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

Upvotes: 2

gvm
gvm

Reputation: 1128

Try this

SELECT sum(Score)/count(Name) as average FROM tablename where Name regexp "Mary";

Upvotes: 0

David Z.
David Z.

Reputation: 5701

This is very difficult to do inside of MySQL. It's great at storing and retrieving relational values, not so great doing this sort of computation. If you don't mind doing this inside of PHP, you can use the code below to count the frequencies.

foreach ($rows as $row)
{
    foreach (explode(" ", $row['Name']) as $token)
    {
        $total[$token] += (int)$row['Score']; 
        $occurances[$token]++;
    }
}

// compute average by taking ($total[$token])/($occurances[$token])

Upvotes: 3

Related Questions