Jim, Thanks for your suggestion. I get an error using this though - "First is no t a recognized SQL function" or something along those lines.
Any ideas what I might be doing wrong? Thanks, Paul ---------- Original Message ---------------------------------- from: "Jim McAtee" <[EMAIL PROTECTED]> Reply-To: [EMAIL PROTECTED] date: Sun, 10 Feb 2002 19:50:03 -0700 >Taking Ian's query, and assuming you've defined a date range with a begin >and end date, something like this should work: > >SELECT First(scipt_name) AS page, Count(script_name) AS pageviews >FROM log >WHERE date BETWEEN #CreateODBCDate(begindate)# > AND #CreateODBCDate(enddate)# >GROUP BY script_name >ORDER BY Count(script_name) DESC > > >Jim > > >----- Original Message ----- >From: "Paul Sinclair" <[EMAIL PROTECTED]> >To: "CF-Talk" <[EMAIL PROTECTED]> >Sent: Sunday, February 10, 2002 7:36 PM >Subject: RE: SQL ?: Counting number of times for this > > >> 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 w e >> >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 talkin g >> >>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 sh ow: >> >> >> >> >>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 vario us >> >>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 o f >> >>bizarre combinations with the various aggregate functions. I think I'v e >l >> o >> >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/cf-talk@houseoffusion.com/ Unsubscribe: http://www.houseoffusion.com/index.cfm?sidebar=lists
