Count row occurrences in a MySQL column

Question

I have a table that stores information from several twitter tweets, including the tweet text and the screen name of the user who tweeted the tweet. Tweets contain hashtags (starting with C #), I want to count the number of hashtags that the user tweeted:

tweet_id | tweet_text | screen_name | -------------------------------------------------------------------------------------------- 1 | #hashtag1 #otherhashtag2 #hashtag3 some more text | tweeter_user_1 | 2 | some text #hashtag1 #hashtag4 more text | tweeter_user_2 | 3 | #hashtag5 #hashtag1 @not a hashtag some#nothashtag | tweeter_user_1 | 4 | #hashtag1 with more text | tweeter_user_3 | 5 | #otherhashtag2 #hashtag3,#hashtag4 more text | tweeter_user_1 | 

If I counted the hashtags tweeter_user_1, the result I expect is 8, if I need the hashtags tweeter_user_3, it should return 1. How can I do this, assuming my table name is tweets.

I tried this: SELECT COUNT( * ) FROM tweets WHERE( LENGTH( REPLACE( tweet_text, '#%', '@') = 0 ) ) AND screen_name = 'tweeter_user_1' but it did not work

I would be happy if the result of tweeter_user_1 was also 9: D

Answer 1

This should give you a list of screen_names and the total number of all hashtags that they use.

 SELECT foo.screen_name, SUM(foo.counts) FROM ( SELECT screen_name, LENGTH( tweet_text) - LENGTH(REPLACE(tweet_text, '#', '')) AS counts FROM tweet_table ) as foo GROUP BY foo.screen_name 

But ... this is an unpleasant query if the table is huge. I can specify specific users in an internal select if you just need a count for one user. Like this:

 SELECT foo.screen_name, SUM(foo.counts) FROM ( SELECT screen_name, LENGTH( tweet_text) - LENGTH(REPLACE(tweet_text, '#', '')) AS counts FROM tweet_table WHERE screen_name = 'tweeter_user_1' ) as foo GROUP BY foo.screen_name 

Answer 2

Depending on how often you need to run the query, you can get MySQL to spend a lot of time analyzing the processor syntax and redraw the tweet_text column. I would highly recommend adding a hashtag_qty column (or similar) and keeping the number of hashtag elements there when you start filling out the row.