Group no. of records by text in a text/varchar field

Create table Test
(Text1 varchar(500))
insert Test values('I love SQL')
insert Test values('SQL rocks')
insert Test values('SQL rocks in 2005')
insert Test values('MS rocks too')
insert Test values('MS is short for microsoft')
So i want to run a query where I would like to group by some key text words
..
So i want to get a count of entries in the table that has words 'SQL' and
'MS' in it
Output should be
KeyWord Count
MS 2
SQL 3
What is the query ? I would eventually add more keywords to the query..
Thanksyou'd want to unpack your input string into a table then it's just a matter
of finding the occurrences.
e.g.
declare @.s varchar(100)
set @.s='MS,SQL'
declare @.padded varchar(8000);set @.padded=','+@.s+','
select s,count(*)
from (select
substring(@.padded,digit+1,charindex(',',
@.padded,digit+1)-digit-1)
from racdigits
where digit <= len(@.padded)-1
and substring(@.padded,digit,1)= ',') derived(s)
join Test on Test.Text1 like '%'+derived.s+'%'
group by s
racdigits is just an auxilary table with value from 1-8000 (i.e. select top
8000 digit=identity(int,1,1) into racdigits from sysobjects,syscolumns)
-oj
"Hassan" <Hassan@.hotmail.com> wrote in message
news:e5HDQH2jGHA.3440@.TK2MSFTNGP02.phx.gbl...
> Create table Test
> (Text1 varchar(500))
> insert Test values('I love SQL')
> insert Test values('SQL rocks')
> insert Test values('SQL rocks in 2005')
> insert Test values('MS rocks too')
> insert Test values('MS is short for microsoft')
> So i want to run a query where I would like to group by some key text
> words ..
> So i want to get a count of entries in the table that has words 'SQL' and
> 'MS' in it
> Output should be
> KeyWord Count
> MS 2
> SQL 3
> What is the query ? I would eventually add more keywords to the query..
> Thanks
>
>|||Where do you want to show data?
If you use front end application, split data there
Madhivanan
Hassan wrote:
> Create table Test
> (Text1 varchar(500))
> insert Test values('I love SQL')
> insert Test values('SQL rocks')
> insert Test values('SQL rocks in 2005')
> insert Test values('MS rocks too')
> insert Test values('MS is short for microsoft')
> So i want to run a query where I would like to group by some key text word
s
> ..
> So i want to get a count of entries in the table that has words 'SQL' and
> 'MS' in it
> Output should be
> KeyWord Count
> MS 2
> SQL 3
> What is the query ? I would eventually add more keywords to the query..
> Thanks|||On Tue, 13 Jun 2006 20:22:47 -0700, Hassan wrote:

>Create table Test
>(Text1 varchar(500))
>insert Test values('I love SQL')
>insert Test values('SQL rocks')
>insert Test values('SQL rocks in 2005')
>insert Test values('MS rocks too')
>insert Test values('MS is short for microsoft')
>So i want to run a query where I would like to group by some key text words
>..
>So i want to get a count of entries in the table that has words 'SQL' and
>'MS' in it
>Output should be
>KeyWord Count
>MS 2
>SQL 3
>What is the query ? I would eventually add more keywords to the query..
Hi Hassan,
Store the keywords in a seperate table, then use a query such as this:
SELECT k.Keyword, COUNT(t.Text1)
FROM Keywords AS k
LEFT JOIN Test AS t
ON t.Text1 LIKE '%' + k.Keyword + '%'
GROUP BY k.Keyword
Hugo Kornelis, SQL Server MVP

Group by Query Insert the Results into Different Table

I am trying to do a select statement and input the result to a different table how can this be done in one step? Now I am just coping to excel and importing back in this is a real pain.

Select Trip, Destination, LeaveDate, LeaveTime, ReturnDate, ReturnTime, Comment, RescheduleDate from Trips

Group by Trip, Destination, LeaveDate, LeaveTime, ReturnDate, ReturnTime, Comment, RescheduleDate

Order by LeaveDate, LeaveTime, Trip

Here it is,

Code Snippet

--For One Time

Select Trip, Destination, LeaveDate, LeaveTime, ReturnDate, ReturnTime, Comment, RescheduleDate into TargetTable from Trips

Group by Trip, Destination, LeaveDate, LeaveTime, ReturnDate, ReturnTime, Comment, RescheduleDate

Order by LeaveDate, LeaveTime, Trip

--For Multiple Times

Create table Targettable

(

Trip int,

Destination varchar(10),

LeaveDate datetime,

LeaveTime varchar(10),

ReturnDate datetime,

ReturnTime varchar(10),

Comment text,

RescheduleDate datetime

)

Insert into Targettable

Select Trip, Destination, LeaveDate, LeaveTime, ReturnDate, ReturnTime, Comment, RescheduleDate from Trips

Group by Trip, Destination, LeaveDate, LeaveTime, ReturnDate, ReturnTime, Comment, RescheduleDate

Order by LeaveDate, LeaveTime, Trip

|||

Just create a table with fields that match those of your query. Then you can run

Code Snippet

INSERT INTO newTable

Select Trip, Destination, LeaveDate, LeaveTime, ReturnDate, ReturnTime, Comment, RescheduleDate from Trips

Group by Trip, Destination, LeaveDate, LeaveTime, ReturnDate, ReturnTime, Comment, RescheduleDate

Order by LeaveDate, LeaveTime, Trip

|||Sweet Thanks!!!

Group By Problem

The following sql statement is giving error when i insert the line Group By...(to get the total amount of Job_id's):

SELECT Isnull(tbl1.Job_no, tbl2.Job_no) As Job_No, tbl1.Amount, tbl1.Entry_id
FROM tbl2 FULL OUTER JOIN tbl1
ON tbl1.colx = tbl2.coly
WHERE <Condition>
GROUP BY Isnull(tbl1.Job_no, tbl2.Job_no);

tbl1 and tbl2 have columns Job_no. But one has a null value if the other value other that null. So the statement above will list the job_no (combined from the two tables), the Amount and the Entry_ID. What i'm trying to arrive at is to add all amount on the same Job_no.

any comment will be greatly appreciated.

Thanks!

Quote:

Originally Posted by Merio

The following sql statement is giving error when i insert the line Group By...(to get the total amount of Job_id's):

SELECT Isnull(tbl1.Job_no, tbl2.Job_no) As Job_No, tbl1.Amount, tbl1.Entry_id
FROM tbl2 FULL OUTER JOIN tbl1
ON tbl1.colx = tbl2.coly
WHERE <Condition>
GROUP BY Isnull(tbl1.Job_no, tbl2.Job_no);

tbl1 and tbl2 have columns Job_no. But one has a null value if the other value other that null. So the statement above will list the job_no (combined from the two tables), the Amount and the Entry_ID. What i'm trying to arrive at is to add all amount on the same Job_no.

any comment will be greatly appreciated.

Thanks!

Instead of:
GROUP BY Isnull(tbl1.Job_no, tbl2.Job_no);
Try
GROUP BY Job_No.

But i belive that that work either,
What you might have to do is group by all tthe other fields
GROUP BY tbl1.Amount, tbl1.Entry_id|||

Quote:

Originally Posted by tezza98

Instead of:
GROUP BY Isnull(tbl1.Job_no, tbl2.Job_no);
Try
GROUP BY Job_No.

But i belive that that work either,
What you might have to do is group by all tthe other fields
GROUP BY tbl1.Amount, tbl1.Entry_id

------------

The problem was solved when i changed the first line with this:
SELECT Isnull(tbl1.Job_no, tbl2.Job_no) As Job_no, SUM(tbl1.amount) As Amount

I needed to put the SUM on tbl1.amount. - I thought that tbl1.amount would be totaled automatically when GROUP BY is used... I was wrong. :0

Thanks for your comment :)