asked on

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 ??

brendonfeeley

SELECT msg, COUNT(*) AS total FROM <table_name> GROUP BY msg ORDER BY total DESC;

Member_2_6478753

ASKER

i want to count only message start with Voting followed by space !!
voting "nickname"

Member_2_6478753

ASKER

and the msg should be start with voting

brendonfeeley

SELECT msg, COUNT(*) AS total FROM <table_name> WHERE LOWER(msg) LIKE 'vote %' GROUP BY msg ORDER BY total DESC;

Member_2_6478753

ASKER

it's dosen;t work with arabic i have replace the word voting by arabic translated word and no result :S

Guy Hengel [angelIII / a3]

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

Member_2_6478753

ASKER

msg like '¿¿¿ %' dosen;t work

Guy Hengel [angelIII / a3]

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 ...

Member_2_6478753

ASKER

i have replaced voting in english with voting in arabic and it's dosen;t work :S

Guy Hengel [angelIII / a3]

can you show sample , so we could try to reproduce it?
I can only tell you that basically the query suggestion does work ...

ASKER CERTIFIED SOLUTION

goldykhurmi

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

goldykhurmi

or
cast (msg as varchar(100)) like N'¿¿¿ %'

or
cast (msg as varchar(100)) like '¿¿¿ %'

Ovid Burke

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

awking00

select replace(message,'Voting ',null) as message, count(*) as cnt
from messages
group by replace(message,'Voting ',null)