<cfquery name="lKeywordFreq" dbtype="query">
select SearchString, count(*) as freq
from qTmp
group by SearchString
order by freq desc
</cfquery>
<cfoutput query="lKeywordFreq" maxrows="1">
<TR vAlign="top">
<TD vAlign="bottom" width="50%" bgColor="E4E4E4">
#lKeywordFreq.SearchString#
</TD>
<TD vAlign="bottom" width="50%">
#lKeywordFreq.Freq#
</TD>
</TR>
</cfoutput>
> 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.
>
><cfquery name="lQuery" dbtype="query">
>SELECT DISTINCT SearchString, DateTime, UserName, IndexName,
>ResultsFound, Status
>FROM qTmp
>ORDER BY IndexName asc
></cfquery>
>
>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
>>
>>
>><cffile
>> action="">
>>
>>
>>file="C:\Inetpub\wwwroot\logReport\dtSearchWeb2003-09.log"
>> variable="fOutput">
>>
>><cfset qTmp =
>>QueryNew("DateTime,UserName,SearchString,IndexName,ResultsFound,
>Status")
>>>
>><cfloop list="#fOutput#" index="fileLine"
>>delimiters="#Chr(10)#">
>><cfif listLen(fileLine) eq 6>
>> <cfscript>
>> 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"));
>> </cfscript>
>></cfif>
>></cfloop>
>>
>>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]
