ntext fields to allow grouping. Damn it's always something simple...
Query looks like this...
----------------------
select
ltrim(rtrim(convert(nvarchar(4000), keywords))) KeyWords,
count(*) records
from sitestats
where ltrim(rtrim(convert(nvarchar(4000), keywords))) <> ''
group by ltrim(rtrim(convert(nvarchar(4000), keywords)))
order by records desc
----------------------
Neal Bailey
Internet Marketing Manager
E-mail: <mailto:[EMAIL PROTECTED]> [EMAIL PROTECTED]
_____
From: Bailey, Neal [mailto:[EMAIL PROTECTED]
Sent: Wednesday, June 30, 2004 11:29 AM
To: CF-Talk
Subject: filtering keywords in table...
Hey guys... I am trying filter a specific table I have that is collecting
every hit to my website, its storing the jsessionID , referring url, and
any keywords if there were any on the referring string.
I want to filter all the keywords/phrases used for this month and give me a
list of number one keywords/phrases used to find my site.
The table is huge and I am having an issue trying to group all the like
keywords/phrases and have it give me an accurate count. There are a lot of
blank keyword fields too. So I need to filter the blank fields out and group
all like keywords and phrases.
Can someone help me figure this out? It's driving me nuts... I have web
trends reporting but have noticed how inaccurate it is with referring urls
from affiliate search engines. So I figured I would store every hit from my
site in a table.
I think my main issue is with the SQL statement, (I really suck at SQL) I
have been goggling for the past few days trying to get this working. I
thought it was easy but I guess not... Here is how my table is set up.
Table name: SiteStats
Fields are:
SequenceID
SessionID
Referer
Keywords.
There are a few other fields but these are the ones I need to use.
Thanks for any help on this...
Neal Bailey
Internet Marketing Manager
E-mail: <mailto:[EMAIL PROTECTED]> [EMAIL PROTECTED]
_____
[Todays Threads] [This Message] [Subscription] [Fast Unsubscribe] [User Settings] [Donations and Support]
