how i can count  the number of message REPEATED

AFIF JABADO
AFIF JABADO used Ask the Experts™
on
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 ??
Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
View Solution Only
brendonfeeley

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

Author

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

Author

Commented:
and  the msg should be start with voting
Acronis in Gartner 2019 MQ for datacenter backup

It is an honor to be featured in Gartner 2019 Magic Quadrant for Datacenter Backup and Recovery Solutions. Gartner’s MQ sets a high standard and earning a place on their grid is a great affirmation that Acronis is delivering on our mission to protect all data, apps, and systems.

Access the MQ here
brendonfeeley

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

Author

Commented:
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]Billing Engineer
Most Valuable Expert 2014
Top Expert 2009

Commented:
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

AFIF JABADO

Author

Commented:
msg like '¿¿¿  %' dosen;t work
Guy Hengel [angelIII / a3]Billing Engineer
Most Valuable Expert 2014
Top Expert 2009

Commented:
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 ...
AFIF JABADO

Author

Commented:
i have replaced voting in english with voting in arabic and it's dosen;t work :S
Guy Hengel [angelIII / a3]Billing Engineer
Most Valuable Expert 2014
Top Expert 2009

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

msg like N'¿¿¿  %'
goldykhurmi

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

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

Commented:
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

awking00Information Technology Specialist

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

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial