1

I have the following tables in the database.

users
-----------------
| id | username |
-----------------
|  1 | goje     |
|  2 | john     |
|  3 | henry    |
-----------------

comments
-------------------------------------------------
| id | uid |          comment                   |
-------------------------------------------------
|  1 |  1  | One who works hard is the one who  |
|    |     | succeeds                           |
|  2 |  1  | This one was the best.             |
|  3 |  2  | You are one of my best friends.    |
|  4 |  3  | He was the one who bought this one.|
-------------------------------------------------

Now, lets suppose the given word is "one". I would like to have a MySQL query that can let me know the number of occurrences of this word for each user in the users table. I this case the result should be 

--------------------
| username | count |
--------------------
| goje     | 3     |   -> 2 in first comment and 1 in second
| henry    | 2     |   -> 2 in henry's only comment
| john     | 1     | 
--------------------

Kindly let me know how can I achieve this using SQL. Thanks!!

Goje87
  • 2,839
  • 7
  • 28
  • 48
  • I think the best you can use is a common query with regexp: http://dev.mysql.com/doc/refman/5.1/en/regexp.html – Leandro Bardelli Feb 09 '12 at 17:13
  • The query that was deleted, doesnt answer the case on: 2 in first comment. That will return just 1 because count, counts records, not words. The only way to do that is parsing the text, like with REGEXP. – Leandro Bardelli Feb 09 '12 at 17:19
  • 1
    http://stackoverflow.com/questions/738282/how-do-you-count-the-number-of-occurrences-of-a-certain-substring-in-a-sql-varch – J Cooper Feb 09 '12 at 17:27

1 Answers1

3

Try this query :

SELECT
 users.username AS username,
 round(sum((LENGTH(comments.comment) - LENGTH(REPLACE(LOWER(comments.comment),"one", ""))) / LENGTH("one")), 0) AS count
FROM users
 INNER JOIN comments ON comments.uid = users.id
GROUP BY users.id
ORDER BY count DESC;

I dont know if REGEXP approach is less or more expensive.

berty
  • 2,178
  • 11
  • 19
  • AWESOME!!! :) what an approach!! Well I feel even if we try using REGEXP, we would be able to obtain only the number of records having the word but not the number of occurrences. – Goje87 Feb 09 '12 at 17:56