SQL Code to Group Data

I have a MSSQL database and it captures information which we are logging. which contains information for up to 5 different topics.

 /****** Object:  Table [dbo].[tbl_messages]    Script Date: 09/23/2014 16:41:04 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[tbl_messages](
      [autoid] [int] IDENTITY(1,1) NOT NULL,
      [message] [nvarchar](max) NULL,
      [sourceaddress] [nvarchar](50) NULL,
      [destinationaddress] [nvarchar](50) NULL,
      [datecaptured] [datetime] NULL,
      [ipaddress] [nvarchar](200) NULL,
      [querystringpassed] [nvarchar](max) NULL
) ON [PRIMARY]
GO


I have messages targeted at different topics listed in the message body. There are 5 topics but people can send messages like

I would like mor information on the Brown Family. Thanks
Please put in my vote for the Green Family
This is a shout out to the Red Family

etc,,,

I want to group the messages based on specific familys . In this case brown, green and red

How can this be done please.?
LVL 1
souldjAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Big MontySenior Web Developer / CEO of ExchangeTree.org Commented:
are you trying to get all record where a certain field contains the family color? if so, this should work:

select * from tbl_messages where message LIKE '%red%';

if I've mis-understood your question, please clarify
0
Kyle AbrahamsSenior .Net DeveloperCommented:
is it always  ______ Family?  

If so you can

1) reverse the string
2) look for the second space after 'ylimaF'   (family reversed)
3) Take substring from charindex(reverse(message),'ylimaF') to the second space.
0
souldjAuthor Commented:
ok, basically it does not always have "family" in the string.

I need to plot a graph of

Family Name Vs Number of Mentions

So

Brown Family SQL would be select count(*) AS familymentions from tbl_messages where message LIKE '%brown%';

But what if I want the SQL statement to count ALL Brown, Red, Etc... and then Group Them.
0
Cloud Class® Course: Python 3 Fundamentals

This course will teach participants about installing and configuring Python, syntax, importing, statements, types, strings, booleans, files, lists, tuples, comprehensions, functions, and classes.

Scott PletcherSenior DBACommented:
What do you mean by "group them"?  You want only a count by last name, not any details from the message row.
0
Kyle AbrahamsSenior .Net DeveloperCommented:
you could use the charindex to check for existence of the word

where
charindex(message, 'brown') > 0 or
charindex(message, 'red') > 0 or ...



if you're looking for separate columns:
select sum(case when charindex(message, 'brown') > 0  then 1 else 0 end) as brownCnt,
          sum(case when charindex(message, 'red') > 0  then 1 else 0 end) as redCnt
from familymentions

it might be helpful if you posted a sample set and your expected output.
0
souldjAuthor Commented:
Attached is 10 records, I have over 10,000 and building.

I need to plot a real time graph of messages on the system versus specific family mentions which will be in the SQL code.
Screen-Shot-2014-09-23-at-5.25.13-PM.png
0
Kyle AbrahamsSenior .Net DeveloperCommented:
I don't see any relations here.  Do you have another table of possible family names?
0
souldjAuthor Commented:
ok, the format of the messages is

"TV [Space] Message"

They are send text messages to a system which is then logging the messages.

The 4 Families are

1. Utere Family

2. Odigie Family

3. Uzodinma Family

4. Unanka Family


Now, People will send messages like

"TV I vote for the Odigie Family"

or

"TV I think the Unanka guys are great!"

Now, I want to query the whole DB and count all instanaces of the various family names so that I get a table like

Utere Family 300
Odigie Family  150
Uzodinma Family 600
Unanka Family 1200

Does this now make sense?
0
Kyle AbrahamsSenior .Net DeveloperCommented:
you can do the following then:

select sum(case when charindex(message, 'Utere') > 0  then 1 else 0 end) as UtereCnt,
          sum(case when charindex(message, 'Odigie') > 0  then 1 else 0 end) as OdigieCnt
-- repeat for others
from familymentions

It will count any instance of the word where Utere is mentioned, or odigie is mentioned, etc.
0
souldjAuthor Commented:
it does not do this.

Infact all values are zero in a 10,000 line DB
Screen-Shot-2014-09-23-at-6.03.07-PM.png
0
Scott PletcherSenior DBACommented:
He's got the charindex params backwards.  Do you need to look for first names as well, since you want "family member" mentions?  

Btw, screen shots of data are useless to me, as I don't plan to re-type it myself; INSERT statements would give me test data I can actually use.


select sum(case when charindex('Utere', message) > 0  then 1 else 0 end) as UtereCnt,
           sum(case when charindex('Odigie', message) > 0  then 1 else 0 end) as OdigieCnt
 -- repeat for others
 from familymentions
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
souldjAuthor Commented:
Excellent Code and Speedy solution!
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft SQL Server 2008

From novice to tech pro — start learning today.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.