SQL Code to Group Data

Posted on 2014-09-23
Medium Priority
Last Modified: 2014-09-23
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 ******/
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

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


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

How can this be done please.?
Question by:souldj
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
  • 4
  • 2
  • +1
LVL 33

Expert Comment

by:Big Monty
ID: 40339544
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
LVL 40

Expert Comment

by:Kyle Abrahams
ID: 40339570
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.

Author Comment

ID: 40339630
ok, basically it does not always have "family" in the string.

I need to plot a graph of

Family Name Vs Number of Mentions


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.
Get real performance insights from real users

Key features:
- Total Pages Views and Load times
- Top Pages Viewed and Load Times
- Real Time Site Page Build Performance
- Users’ Browser and Platform Performance
- Geographic User Breakdown
- And more

LVL 69

Expert Comment

by:Scott Pletcher
ID: 40339640
What do you mean by "group them"?  You want only a count by last name, not any details from the message row.
LVL 40

Expert Comment

by:Kyle Abrahams
ID: 40339646
you could use the charindex to check for existence of the word

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.

Author Comment

ID: 40339668
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.
LVL 40

Expert Comment

by:Kyle Abrahams
ID: 40339682
I don't see any relations here.  Do you have another table of possible family names?

Author Comment

ID: 40339699
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"


"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?
LVL 40

Assisted Solution

by:Kyle Abrahams
Kyle Abrahams earned 1000 total points
ID: 40339740
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.

Author Comment

ID: 40339774
it does not do this.

Infact all values are zero in a 10,000 line DB
LVL 69

Accepted Solution

Scott Pletcher earned 1000 total points
ID: 40339782
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

Author Closing Comment

ID: 40340219
Excellent Code and Speedy solution!

Featured Post

Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

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

After restoring a Microsoft SQL Server database (.bak) from backup or attaching .mdf file, you may run into "Error '15023' User or role already exists in the current database" when you use the "User Mapping" SQL Management Studio functionality to al…
There have been several questions about Large Transaction Log Files in SQL Server 2008, and how to get rid of them when disk space has become critical. This article will explain how to disable full recovery and implement simple recovery that carries…
Add bar graphs to Access queries using Unicode block characters. Graphs appear on every record in the color you want. Give life to numbers. Hopes this gives you ideas on visualizing your data in new ways ~ Create a calculated field in a query: …
In this video, Percona Solution Engineer Rick Golba discuss how (and why) you implement high availability in a database environment. To discuss how Percona Consulting can help with your design and architecture needs for your database and infrastr…

800 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