how i can count the number of message REPEATED

i have a table contain a msg field
and the message format is like :

Vote george
vote mbuzo
 format of the message is VOTE "NICKNAME"

I WANT TO COUNT THE NUMBER OF MSG FOR EACH NICKNAME ??
LVL 1
AFIF JABADOAsked:
Who is Participating?
 
goldykhurmiConnect With a Mentor Commented:
Try to write like this :

msg like N'¿¿¿  %'
0
 
brendonfeeleyCommented:
SELECT msg, COUNT(*) AS total FROM <table_name> GROUP BY msg ORDER BY total DESC;
0
 
AFIF JABADOAuthor Commented:
i want to count only message start with Voting followed by space !!
voting "nickname"
0
Upgrade your Question Security!

Your question, your audience. Choose who sees your identity—and your question—with question security.

 
AFIF JABADOAuthor Commented:
and  the msg should be start with voting
0
 
brendonfeeleyCommented:
SELECT msg, COUNT(*) AS total FROM <table_name> WHERE LOWER(msg) LIKE 'vote %' GROUP BY msg ORDER BY total DESC;
0
 
AFIF JABADOAuthor Commented:
it's dosen;t work with arabic  i have replace the word voting by arabic translated word and no result :S
0
 
Guy Hengel [angelIII / a3]Billing EngineerCommented:
no points here, you have to use the correct "collate" keyword:
SELECT msg, COUNT(*) AS total 
FROM <table_name> 
WHERE LOWER(msg) LIKE 'vote %' COLLATE _your_collation_name_goes_here 
GROUP BY msg ORDER BY total DESC; 

Open in new window

0
 
AFIF JABADOAuthor Commented:
msg like '¿¿¿  %' dosen;t work
0
 
Guy Hengel [angelIII / a3]Billing EngineerCommented:
well, I asked you to use COLLATE keyword ...
anyhow, with "arabic" data, I am not 100% sure how this works out, but it should be straightforward ...
0
 
AFIF JABADOAuthor Commented:
i have replaced voting in english with voting in arabic and it's dosen;t work :S
0
 
Guy Hengel [angelIII / a3]Billing EngineerCommented:
can you show sample , so we could try to reproduce it?
I can only tell you that basically the query suggestion does work ...
0
 
goldykhurmiCommented:
or
cast (msg as varchar(100)) like N'¿¿¿  %'

or
cast (msg as varchar(100)) like '¿¿¿  %'
0
 
Ovid BurkeConsultant InstructorCommented:
Try this:
SELECT DISTINCT(REPLACE(message,'Vote ', '')) AS message, COUNT(*) AS votes
FROM _messages
GROUP BY message ORDER BY votes DESC

Open in new window

0
 
awking00Commented:
select replace(message,'Voting ',null) as message, count(*) as cnt
from messages
group by replace(message,'Voting ',null)
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.