Solved

how i can count  the number of message REPEATED

Posted on 2012-12-25
14
334 Views
Last Modified: 2013-01-09
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
Comment
Question by:afifosh
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 5
  • 3
  • 2
  • +3
14 Comments
 
LVL 4

Expert Comment

by:brendonfeeley
ID: 38720033
SELECT msg, COUNT(*) AS total FROM <table_name> GROUP BY msg ORDER BY total DESC;
0
 
LVL 1

Author Comment

by:afifosh
ID: 38720041
i want to count only message start with Voting followed by space !!
voting "nickname"
0
 
LVL 1

Author Comment

by:afifosh
ID: 38720042
and  the msg should be start with voting
0
Best Practices: Disaster Recovery Testing

Besides backup, any IT division should have a disaster recovery plan. You will find a few tips below relating to the development of such a plan and to what issues one should pay special attention in the course of backup planning.

 
LVL 4

Expert Comment

by:brendonfeeley
ID: 38720088
SELECT msg, COUNT(*) AS total FROM <table_name> WHERE LOWER(msg) LIKE 'vote %' GROUP BY msg ORDER BY total DESC;
0
 
LVL 1

Author Comment

by:afifosh
ID: 38720114
it's dosen;t work with arabic  i have replace the word voting by arabic translated word and no result :S
0
 
LVL 143

Expert Comment

by:Guy Hengel [angelIII / a3]
ID: 38720131
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
 
LVL 1

Author Comment

by:afifosh
ID: 38720139
msg like '¿¿¿  %' dosen;t work
0
 
LVL 143

Expert Comment

by:Guy Hengel [angelIII / a3]
ID: 38720147
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
 
LVL 1

Author Comment

by:afifosh
ID: 38720175
i have replaced voting in english with voting in arabic and it's dosen;t work :S
0
 
LVL 143

Expert Comment

by:Guy Hengel [angelIII / a3]
ID: 38720187
can you show sample , so we could try to reproduce it?
I can only tell you that basically the query suggestion does work ...
0
 
LVL 1

Accepted Solution

by:
goldykhurmi earned 500 total points
ID: 38720862
Try to write like this :

msg like N'¿¿¿  %'
0
 
LVL 1

Expert Comment

by:goldykhurmi
ID: 38720863
or
cast (msg as varchar(100)) like N'¿¿¿  %'

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

Expert Comment

by:Ovid Burke
ID: 38721062
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
 
LVL 32

Expert Comment

by:awking00
ID: 38721360
select replace(message,'Voting ',null) as message, count(*) as cnt
from messages
group by replace(message,'Voting ',null)
0

Featured Post

Free Tool: Port Scanner

Check which ports are open to the outside world. Helps make sure that your firewall rules are working as intended.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

Title # Comments Views Activity
Database Mail Profiles 1 52
I am new to using JSON in SQL Server 2 58
HIghlights of SSIS? 3 45
mysql workbecn having problems to export tables to cvs 4 28
Confronted with some SQL you don't know can be a daunting task. It can be even more daunting if that SQL carries some of the old secret codes used in the Ye Olde query syntax, such as: (+)     as used in Oracle;     *=     =*    as used in Sybase …
This article explains how to reset the password of the sa account on a Microsoft SQL Server.  The steps in this article work in SQL 2005, 2008, 2008 R2, 2012, 2014 and 2016.
Finds all prime numbers in a range requested and places them in a public primes() array. I've demostrated a template size of 30 (2 * 3 * 5) but larger templates can be built such 210  (2 * 3 * 5 * 7) or 2310  (2 * 3 * 5 * 7 * 11). The larger templa…

751 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question