Destiny Amana
asked on
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.?
/****** 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.?
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.
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)
ASKER
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.
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.
What do you mean by "group them"? You want only a count by last name, not any details from the message row.
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.
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.
ASKER
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
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
I don't see any relations here. Do you have another table of possible family names?
ASKER
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?
"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?
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
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
Infact all values are zero in a 10,000 line DB
Screen-Shot-2014-09-23-at-6.03.07-PM.png
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
Excellent Code and Speedy solution!
select * from tbl_messages where message LIKE '%red%';
if I've mis-understood your question, please clarify