Ah, I get it - so the table lists each TIME one page is viewed? Is that right?
If you can tell me the database type, and maybe give 2 sample rows of data, it'll be easier to figure out. We typically handle stuff like this with a storedproc. Here's a query we wrote in Access to do what you're describing, only in a store searches database. This query pulls the duplicate cities stored in the database, and returns the city and count: SELECT First([searches].[city]) AS cityF, Count([searches].[city]) AS NumberOfDups FROM searches WHERE city<>"" GROUP BY [searches].[city] HAVING (((Count(searches.city))>5)) ORDER BY count([city]) DESC; Ian -----Original Message----- From: Paul Sinclair [mailto:[EMAIL PROTECTED]] Sent: Sunday, February 10, 2002 2:32 PM To: CF-Talk Subject: RE: SQL ?: Counting number of times for this Ian, I'm sorry - I don't know what you mean. What is "COUNTER" and "TITLE"? I'm assuming you're meaning that these would be fields in the table? Are you saying I should just select a couple fields from the table and then output them? That's what I am reading from your post but I am probably mis sing something. All that does for me is list the records in whatever order I selected them but it doesn't count them and then order them by which page s were viewed most often. Thanks for your help - sorry to be thick. Paul Sinclair ---------- Original Message ---------------------------------- from: "Ian Lurie" <[EMAIL PROTECTED]> Reply-To: [EMAIL PROTECTED] date: Sun, 10 Feb 2002 13:42:55 -0800 >Assuming you're keeping track of page views in the table you're talking >about, just do a query like this: > >SELECT COUNTER, TITLE >FROM TABLE >ORDER BY COUNTER DESC > >Then do a CFOUTPUT with Maxrows = the number of pages you want to show: > >COUNTER: TITLE >COUNTER: TITLE > >Etc. > >I may be missing something, but this should do it... > >-----Original Message----- >From: Paul Sinclair [mailto:[EMAIL PROTECTED]] >Sent: Sunday, February 10, 2002 1:39 PM >To: CF-Talk >Subject: SQL ?: Counting number of times for this > > >I'm preparing a simple homebrewed site statistics report for a client. I >need to send a cfmail each night showing the top pages viewed in various >areas of the site. I'm stumped on how to get the info I need from the db. > >The table involved is a simple 5 column table. I just need to do a sql que ry >that will look at the table and figure out which pages were viewed the mos t >times during the previous 24 hours and then output the list of most popula r >pages in descending order of popularity. > >How do I do the query that will pull that out? I've tried all manner of >bizarre combinations with the various aggregate functions. I think I've lo st >site of the forest through the trees here. > >Thanks, >Paul Sinclair > > ______________________________________________________________________ Why Share? Dedicated Win 2000 Server � PIII 800 / 256 MB RAM / 40 GB HD / 20 GB MO/XFER Instant Activation � $99/Month � Free Setup http://www.pennyhost.com/redirect.cfm?adcode=coldfusionc FAQ: http://www.thenetprofits.co.uk/coldfusion/faq Archives: http://www.mail-archive.com/[email protected]/ Unsubscribe: http://www.houseoffusion.com/index.cfm?sidebar=lists
