Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 338
  • Last Modified:

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 ??
0
AFIF JABADO
Asked:
AFIF JABADO
  • 5
  • 3
  • 2
  • +3
1 Solution
 
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
 
AFIF JABADOAuthor Commented:
and  the msg should be start with voting
0
What does it mean to be "Always On"?

Is your cloud always on? With an Always On cloud you won't have to worry about downtime for maintenance or software application code updates, ensuring that your bottom line isn't affected.

 
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:
Try to write like this :

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

or
cast (msg as varchar(100)) like '¿¿¿  %'
0
 
Ovid BurkeCreative DirectorCommented:
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

Featured Post

 [eBook] Windows Nano Server

Download this FREE eBook and learn all you need to get started with Windows Nano Server, including deployment options, remote management
and troubleshooting tips and tricks

  • 5
  • 3
  • 2
  • +3
Tackle projects and never again get stuck behind a technical roadblock.
Join Now