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 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
