mysql query to count white spaces or words from entire row

Question

in my database one table is there with say name as tableA.the structure of table is given here ``` id from message 1 23 hi how are you 2 65 hey whats going on 3 74 enjoying the vacation with family 4 23 here in Australia its chilly season 5 74 hey sam whats the plan for tomorrow ``` see id is auto-increment.from column says which user is sending message and the message column consists of messages . i want to know which users has sent how many words .so i have to first count the words from row then i have to search the entire column and find where there is repetiotion and add the words for that particular user.in my example you can see that two places from 23 and 74 is coming. i want my output as ``` from nos 23 10 65 4 74 12 ``` here you can see that 23 is coming in two rows so i have added the no of words of two rows like that 74 is also coming in two rows .please guide

Accepted Answer

You can count words by this query-

```
Select length('string')-length(replace('string',' ',''))+1;
```

For your table you can do it as

```
Select *,(length(message)-length(replace(message,' ',''))+1) from table_name
```

This will display word count of every msg in fron of it.

For your desired result, try this-

```
Select `from`, sum((length(message)-length(replace(message,' ',''))+1)) as nos from table_name group by `from`
```

mysql query to count white spaces or words from entire row

Solution

Problem