Ian, It is a pretty straightforward table in an MSSQL db. Along the lines of thi s example data (simplified for displaying here):
id..script_name......remoteip.......date.......referer 1.../page1.cfm?id=1..200.36.36.201..2/10/2002..pagez.cfm 2.../page1.cfm?id=2..200.36.36.202..2/10/2002..pageb.cfm 3.../page1.cfm?id=3..200.36.36.203..2/10/2002..pagec.cfm 4.../page1.cfm?id=2..200.36.36.204..2/10/2002..paged.cfm 5.../page1.cfm?id=2..200.36.36.205..2/10/2002..pagee.cfm 6.../page1.cfm?id=4..200.36.36.206..2/10/2002..pagef.cfm 7.../page1.cfm?id=1..200.36.36.207..2/10/2002..pageg.cfm I need something that will go through and sum the number of times each page (the "script_name" field) is hit and then output the results in descending order of "popularity." So for the above sample, it would show like this: /page1.cfm?id=2 3 hits /page1.cfm?id=1 2 hits /page1.cfm?id=4 1 hit /page1.cfm?id=3 1 hit Thanks for any more help. I'm sure it is simpler than I am making it. Paul ---------- Original Message ---------------------------------- from: "Ian Lurie" <[EMAIL PROTECTED]> Reply-To: [EMAIL PROTECTED] date: Sun, 10 Feb 2002 15:00:04 -0800 >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, an d >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 the n > output them? That's what I am reading from your post but I am probably mi s >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 pag e >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 qu e >ry >>that will look at the table and figure out which pages were viewed the mo s >t >>times during the previous 24 hours and then output the list of most popul a >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 l o >st >>site of the forest through the trees here. >> >>Thanks, >>Paul Sinclair >> >> > > ______________________________________________________________________ Get Your Own Dedicated Windows 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=coldfusionb FAQ: http://www.thenetprofits.co.uk/coldfusion/faq Archives: http://www.mail-archive.com/cf-talk@houseoffusion.com/ Unsubscribe: http://www.houseoffusion.com/index.cfm?sidebar=lists
