RE: SQL Help - Correct Post
Ok... is it not working for you?
-Mark
-Original Message-
From: ColdFusion Programmer [mailto:[EMAIL PROTECTED]
Sent: Thursday, October 09, 2003 11:07 AM
To: CF-Talk
Subject: Re:SQL Help - Correct Post
Thanks but I'm using this code to get the top 5
select SearchString, count(*) as freq
from qTmp
group by SearchString
order by freq desc
#lKeywordFreq.SearchString#
#lKeywordFreq.Freq#
> Try:
>
>select top 10 count(distinct SearchString) AS total, SearchString
>FROM clients
>GROUP BY SearchString
>order by total desc
>
>-Original Message-
>From: ColdFusion Programmer [mailto:[EMAIL PROTECTED]
>Sent: Thursday, October 09, 2003 6:39 AM
>To: CF-Talk
>Subject: Re:SQL Help - Correct Post
>
>
>The log file stores a log of what the user has been searching on the
>site search. For example if the user searches for
>the keyword "Fusion", the log file stores the information like this:
>
>I want to display the top 5 or 10 search keywords that users regularly
>search for and because this is logged
>in the log file I'm sure it is possible to get this information.
>Currently I'm displaying all the results with a
>simple sql statment.
>
>
>SELECT DISTINCT SearchString, DateTime, UserName, IndexName,
>ResultsFound, Status
>FROM qTmp
>ORDER BY IndexName asc
>
>
>My sql skills are not very good and was wondering if someone could
>show me how to do this. I would really appreciate
>your help.
>
>Best Regards,
>Allan
>
>>What is your question?
>>-Original Message-----
>>From: Allan Clarke [mailto:[EMAIL PROTECTED]
>>Sent: donderdag 9 oktober 2003 13:32
>>To: CF-Talk
>>Subject: SQL Help - Correct Post
>>
>>
>>
>> action="">
>>
>>
>>file="C:\Inetpub\wwwroot\logReport\dtSearchWeb2003-09.log"
>> variable="fOutput">
>>
>>
>>QueryNew("DateTime,UserName,SearchString,IndexName,ResultsFound,
>Status")
>>>
>>
>>delimiters="#Chr(10)#">
>>
>>
>> tmp = QueryAddRow(qTmp,1);
>> tmp=QuerySetCell(qTmp,"DateTime",
>>Replace(listGetAt(fileLine,1),"""","","All"));
>> tmp=QuerySetCell(qTmp,"UserName",
>>Replace(LCase(listGetAt(fileLine,2)),"""","","All"));
>> tmp=QuerySetCell(qTmp,"SearchString",
>>Replace(listGetAt(fileLine,3),"""","","All"));
>> tmp=QuerySetCell(qTmp,"IndexName",
>>Replace(ListLast(listGetAt(fileLine,4),'\'),"""","","All"));
>> tmp=QuerySetCell(qTmp,"ResultsFound",
>>Replace(listGetAt(fileLine,5),"""","","All"));
>> tmp=QuerySetCell(qTmp,"Status",
>>Replace(listGetAt(fileLine,6),"""","","All"));
>>
>>
>>
>>
>>Hi Everybody,
>>
>>The above code reads the log file and inserts the file
>>content in an empty query with a set of columns.
>>
>>The log file stores a log of what the user has been
>>searching on the site search. For example if the user
>>searches for the keyword "Fusion", the log file stores
>>the information like this:
>>
>>Date/Time Keyword Category Results Returned
>>Status UserName
>>
>> _
>>
>>
[Todays Threads]
[This Message]
[Subscription]
[Fast Unsubscribe]
[User Settings]
RE: SQL Help - Correct Post
Try:
select top 10 count(distinct SearchString) AS total, SearchString
FROM clients
GROUP BY SearchString
order by total desc
-Original Message-
From: ColdFusion Programmer [mailto:[EMAIL PROTECTED]
Sent: Thursday, October 09, 2003 6:39 AM
To: CF-Talk
Subject: Re:SQL Help - Correct Post
The log file stores a log of what the user has been searching on the site search. For example if the user searches for
the keyword "Fusion", the log file stores the information like this:
I want to display the top 5 or 10 search keywords that users regularly search for and because this is logged
in the log file I'm sure it is possible to get this information. Currently I'm displaying all the results with a
simple sql statment.
SELECT DISTINCT SearchString, DateTime, UserName, IndexName, ResultsFound, Status
FROM qTmp
ORDER BY IndexName asc
My sql skills are not very good and was wondering if someone could show me how to do this. I would really appreciate
your help.
Best Regards,
Allan
>What is your question?
>-Original Message-
>From: Allan Clarke [mailto:[EMAIL PROTECTED]
>Sent: donderdag 9 oktober 2003 13:32
>To: CF-Talk
>Subject: SQL Help - Correct Post
>
>
>
> action="">
>
>
>file="C:\Inetpub\wwwroot\logReport\dtSearchWeb2003-09.log"
> variable="fOutput">
>
>
>QueryNew("DateTime,UserName,SearchString,IndexName,ResultsFound,Status")
>>
>
>delimiters="#Chr(10)#">
>
>
> tmp = QueryAddRow(qTmp,1);
> tmp=QuerySetCell(qTmp,"DateTime",
>Replace(listGetAt(fileLine,1),"""","","All"));
> tmp=QuerySetCell(qTmp,"UserName",
>Replace(LCase(listGetAt(fileLine,2)),"""","","All"));
> tmp=QuerySetCell(qTmp,"SearchString",
>Replace(listGetAt(fileLine,3),"""","","All"));
> tmp=QuerySetCell(qTmp,"IndexName",
>Replace(ListLast(listGetAt(fileLine,4),'\'),"""","","All"));
> tmp=QuerySetCell(qTmp,"ResultsFound",
>Replace(listGetAt(fileLine,5),"""","","All"));
> tmp=QuerySetCell(qTmp,"Status",
>Replace(listGetAt(fileLine,6),"""","","All"));
>
>
>
>
>Hi Everybody,
>
>The above code reads the log file and inserts the file
>content in an empty query with a set of columns.
>
>The log file stores a log of what the user has been
>searching on the site search. For example if the user
>searches for the keyword "Fusion", the log file stores
>the information like this:
>
>Date/Time Keyword Category Results Returned
>Status UserName
>
> _
>
>
[Todays Threads]
[This Message]
[Subscription]
[Fast Unsubscribe]
[User Settings]
Re: SQL Help - Correct Post
Allan Clarke wrote: > > Date/Time Keyword Category Results Returned > Status UserName You probably have a long line of dashes or equals signs that is stripped as a signature delimiter. Jochem [Todays Threads] [This Message] [Subscription] [Fast Unsubscribe] [User Settings]
RE: SQL Help - Correct Post
What is your question?
-Original Message-
From: Allan Clarke [mailto:[EMAIL PROTECTED]
Sent: donderdag 9 oktober 2003 13:32
To: CF-Talk
Subject: SQL Help - Correct Post
action="">
file="C:\Inetpub\wwwroot\logReport\dtSearchWeb2003-09.log"
variable="fOutput">
QueryNew("DateTime,UserName,SearchString,IndexName,ResultsFound,Status")
>
delimiters="#Chr(10)#">
tmp = QueryAddRow(qTmp,1);
tmp=QuerySetCell(qTmp,"DateTime",
Replace(listGetAt(fileLine,1),"""","","All"));
tmp=QuerySetCell(qTmp,"UserName",
Replace(LCase(listGetAt(fileLine,2)),"""","","All"));
tmp=QuerySetCell(qTmp,"SearchString",
Replace(listGetAt(fileLine,3),"""","","All"));
tmp=QuerySetCell(qTmp,"IndexName",
Replace(ListLast(listGetAt(fileLine,4),'\'),"""","","All"));
tmp=QuerySetCell(qTmp,"ResultsFound",
Replace(listGetAt(fileLine,5),"""","","All"));
tmp=QuerySetCell(qTmp,"Status",
Replace(listGetAt(fileLine,6),"""","","All"));
Hi Everybody,
The above code reads the log file and inserts the file
content in an empty query with a set of columns.
The log file stores a log of what the user has been
searching on the site search. For example if the user
searches for the keyword "Fusion", the log file stores
the information like this:
Date/Time Keyword Category Results Returned
Status UserName
_
[Todays Threads]
[This Message]
[Subscription]
[Fast Unsubscribe]
[User Settings]
SQL Help - Correct Post
action="">
file="C:\Inetpub\wwwroot\logReport\dtSearchWeb2003-09.log"
variable="fOutput">
QueryNew("DateTime,UserName,SearchString,IndexName,ResultsFound,Status")>
delimiters="#Chr(10)#">
tmp = QueryAddRow(qTmp,1);
tmp=QuerySetCell(qTmp,"DateTime",
Replace(listGetAt(fileLine,1),,"","All"));
tmp=QuerySetCell(qTmp,"UserName",
Replace(LCase(listGetAt(fileLine,2)),,"","All"));
tmp=QuerySetCell(qTmp,"SearchString",
Replace(listGetAt(fileLine,3),,"","All"));
tmp=QuerySetCell(qTmp,"IndexName",
Replace(ListLast(listGetAt(fileLine,4),'\'),,"","All"));
tmp=QuerySetCell(qTmp,"ResultsFound",
Replace(listGetAt(fileLine,5),,"","All"));
tmp=QuerySetCell(qTmp,"Status",
Replace(listGetAt(fileLine,6),,"","All"));
Hi Everybody,
The above code reads the log file and inserts the file
content in an empty query with a set of columns.
The log file stores a log of what the user has been
searching on the site search. For example if the user
searches for the keyword "Fusion", the log file stores
the information like this:
Date/Time Keyword Category Results Returned
Status UserName
[Todays Threads]
[This Message]
[Subscription]
[Fast Unsubscribe]
[User Settings]
SQL Help - Correct Post
action="">
file="C:\Inetpub\wwwroot\logReport\dtSearchWeb2003-09.log"
variable="fOutput">
QueryNew("DateTime,UserName,SearchString,IndexName,ResultsFound,Status")>
delimiters="#Chr(10)#">
tmp = QueryAddRow(qTmp,1);
tmp=QuerySetCell(qTmp,"DateTime",
Replace(listGetAt(fileLine,1),,"","All"));
tmp=QuerySetCell(qTmp,"UserName",
Replace(LCase(listGetAt(fileLine,2)),,"","All"));
tmp=QuerySetCell(qTmp,"SearchString",
Replace(listGetAt(fileLine,3),,"","All"));
tmp=QuerySetCell(qTmp,"IndexName",
Replace(ListLast(listGetAt(fileLine,4),'\'),,"","All"));
tmp=QuerySetCell(qTmp,"ResultsFound",
Replace(listGetAt(fileLine,5),,"","All"));
tmp=QuerySetCell(qTmp,"Status",
Replace(listGetAt(fileLine,6),,"","All"));
Hi Everybody,
The above code reads the log file and inserts the file
content in an empty query with a set of columns.
The log file stores a log of what the user has been
searching on the site search. For example if the user
searches for the keyword "Fusion", the log file stores
the information like this:
Date/Time Keyword Category Results Returned
Status UserName
[Todays Threads]
[This Message]
[Subscription]
[Fast Unsubscribe]
[User Settings]
