sql - Need help building hash tag trending query in mysql -

i'm trying build query measures "trending" hash tags tweets. have table tweets , column message (varchar)

basically want group messages hash tag breaks down if there more 1 hash tag in message field, heres have far

select count(message) count, `message` `tweets` `message` rlike '^#[[:alnum:]]' group `tweets`.`message` order `count` desc 

which output following rows

"4","#sushi" "3","#coffee" "3","#wine" "1","#coffee #park" "1","#drinking" "1","#steak" "1","#pizza" "1","#pasta" "1","#food" 

the fourth row problem want both hash tags totaled on own, not sure if can achieve mysql.

your column message want group hashtags. database design pov, table isn't in 1nf because message holds repeating group of hash tags. won't able use power of sql on table until that's corrected.

what need table hashtags has 1 row every tag in every message. once have that, query practically writes itself.