Solved

how to count rows which match various words, broken  down by month

Posted on 2011-03-06
6
304 Views
Last Modified: 2012-05-11
I have a table 'txts' with columns like 'message' (255 varchar) 'txttime' (datetime) and I would like to count all the rows which 'message' contains any of the words or phrases 'bully, bullied, blly' and want to know how many times they appeared in a given daterange 'txttime' but broken down into monthly occurrence ...

Seems straight forward enough but I can't figure it out.

The dataset contains 350000 rows per year of data.
0
Comment
Question by:timbo007
[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
  • 3
  • 3
6 Comments
 
LVL 15

Expert Comment

by:derekkromm
ID: 35048835
select month(txttime), year(txttime), count(*) from txts
where (message like '%bully%' or message like '%bullied%' or message like '%blly%')
group by month(txttime), year(txttime)
0
 

Author Comment

by:timbo007
ID: 35050914
Thanks, but this captures other words which have these letters in them too, I guess most of the time it would have spaces around words but sometimes it could be at the beginning or end of the data or also it could have a ',' or a '.' I guess, the data is TXT messages that you would send with your cell phone.. I have a list of about 100 different sets of these key words I am running in a loop...
0
 
LVL 15

Accepted Solution

by:
derekkromm earned 500 total points
ID: 35050995
well, it'll find what you tell it to find ;)

is there anything else i can help with?
0
Command Line Tips and Tricks

The command line is a powerful tool at the disposal of every Linux user. Although Linux distros come with beautiful user interfaces, it's worthwhile to learn the command line because it allows you to do a number of things that you otherwise cannot do from the GUI.  

 

Author Comment

by:timbo007
ID: 35051055
yes, can you please tell me how to get those words as the result, I don't think using like '%word%' is good enough, how can it know if there are full stops or at the beginning etc? would using a regex be better? though this is going over 350000 rows so I guess regex would be slow, I am happy to test it though but don't know the syntax
0
 
LVL 15

Expert Comment

by:derekkromm
ID: 35051153
at the beginning and end you'd have to do:

left(message, 5) = 'bully' or left(message, 4) = 'blly'
or right(message, 5) = 'bully' or right(message, 4) = 'blly'

etc

if you want to check for spaces, you'd have to do message like '% bully %'

using regex (assuming you're using mysql): http://www.tech-recipes.com/rx/484/use-regular-expressions-in-mysql-select-statements/

0
 

Author Closing Comment

by:timbo007
ID: 35052334
Thanks, could be explained with better examples though but I can figure the rest now out cheers :)
0

Featured Post

Congratulations! You’re Certified – Now What?

Starting a new career can be overwhelming. Becoming certified in your field of expertise is a great start, but where do you go from here?  Here are some tips to help you on your career journey.

Question has a verified solution.

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

This article shows the steps required to install WordPress on Azure. Web Apps, Mobile Apps, API Apps, or Functions, in Azure all these run in an App Service plan. WordPress is no exception and requires an App Service Plan and Database to install
Containers like Docker and Rocket are getting more popular every day. In my conversations with customers, they consistently ask what containers are and how they can use them in their environment. If you’re as curious as most people, read on. . .
This tutorial will teach you the special effect of super speed similar to the fictional character Wally West aka "The Flash" After Shake : http://www.videocopilot.net/presets/after_shake/ All lightning effects with instructions : http://www.mediaf…
Sometimes it takes a new vantage point, apart from our everyday security practices, to truly see our Active Directory (AD) vulnerabilities. We get used to implementing the same techniques and checking the same areas for a breach. This pattern can re…

635 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