Link to home
Start Free TrialLog in
Avatar of kenjpete
kenjpete

asked on

Problem with SQL Query on ColdFusion Web Page

I have a ColdFusion web page with a keyword search box that allows users to search for information in our MS Access database. That same keyword box also inserts any searches done into a database table called TBL_stats.

I want to query that table and show the most popular keywords, along with a count of how many times a particular keyword/phrase has been searched on.

I am having trouble figuring out the SQL query on how to count the number of occurrences of the same keyword. Unfortunately using COUNT (DISTINCT column name) doesn't work in MS Access. Also, I'm not sure if it would be better to pull those keywords into a ColdFusion array and count them, or to try and return a SQL recordset with the necessary data.

I would appreciate any help to point me in the right direction.
Avatar of Rey Obrero (Capricorn1)
Rey Obrero (Capricorn1)
Flag of United States of America image

try in access

select [columnName], count([columnName])
from table
group by [columnName]
<no Points wanted>
The SQL above to can also be modified display the top(n) values as well.
Or even all the vales that appear more than(n) times

I am sure Rey can help you with the details...
Avatar of kenjpete
kenjpete

ASKER

Thank you, that worked, in that I was able to pull all the unique keywords in that table.  Now I would like to rank them by the most entered and provide a count of each.

In my research on the COUNT function it suggests using the AS feature, but I'm also wondering how to display that total count number. Would I use SUM or another function for that?
I tried the following and when I dump the recordset it appears to work but I keep getting a syntax error in my ORDER BY clause?

 
SELECT Keywords, COUNT (Keywords) AS TotalKeywords
  FROM TBL_Stats
  GROUP BY Keywords
  ORDER BY TotalKeywords DESC
SELECT Keywords, COUNT (Keywords) AS TotalKeywords
  FROM TBL_Stats
  GROUP BY Keywords
  ORDER BY Keywords DESC
ASKER CERTIFIED SOLUTION
Avatar of Rey Obrero (Capricorn1)
Rey Obrero (Capricorn1)
Flag of United States of America image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
Thats perfect! Thanks Rey....I'm all set now!