Omar Abid Omar Abid - 1 year ago 94
MySQL Question

Calculate the words number in a view in mysql

I have the following table structure

| myID | text |
| 3 | some text |
| 3 | other text |
| 5 | text |

myID is not a unique ID. It can be repeated. Text is arbitrary strings.
I'm looking to create a view that returns the count of words in the text of the myID rows.

| myID | count |
| 3 | 4 |
| 5 | 1 |

Well, I started out (I'm a newbie is mySQL) and did the following

FROM table
GROUP by myid

This does just the grouping. I think the next steps are

  1. How to make pagetext returns the concatenation of the appropriate myID rows (I'm looking to limit the concatenation to 25 rows only).

  2. How to count the number of words in the returned value.

Any ideas how to start?

Answer Source

You can find the number of words in a column (if you can assume there is 1 and exactly 1 space between words) with the following query:

  SUM(LENGTH(text)-LENGTH(REPLACE(text, ' ', ''))+1)
FROM table


Recommended from our users: Dynamic Network Monitoring from WhatsUp Gold from IPSwitch. Free Download