I have a db table that stores basic historical page view data. Each day a
task runs and inserts the date, a record of the pages viewed and a count of
how many times each was viewed. It's easy to select the most viewed pages
for a given date using:
select pname,pagehit
from tbl_log_page_count
where CONVERT(char(8), logdate, 112) =
#DateFormat(DateAdd('d',-1,now()),'yyyymmdd')#
order by pagehit desc
What I'd like to be able to do is aggregate the data for a given date range
so if my table had (date,hits,page) :
15-5-2007 | 2 | /index.cfm
15-5-2007 | 4 | /about-us.cfm
15-5-2007 | 3 | /not-about-us.cfm
14-5-2007 | 1 | /index.cfm
14-5-2007 | 6 | /about-us.cfm
14-5-2007 | 2 | /not-about-us.cfm
I would like to be able to get this for the shown date range:
3 | /index.cfm
10 | /about-us.cfm
5 | not-about-us.cfm
I can achieve what I want by creating a new query, looping over the distinct
pagenames and summing all found hit counts in the time period but it's slow
and seems messy. The date range bit isn't a problem but I can't figure out
if it's possible to sum the pagehit column for each occurence of a page
name. The closest I've got is below but this only sums the hitcount where
the page name and hitcount match (i.e. if I have the same number of page
views for a given page on multiple dates):
select pname,sum(pagehit) as pagehit
where dates between x and y
group by pname, pagehit
order by pagehit desc, pname
Not sure if it's even possible to achieve what I want with a single query.
Any ideas?
Thanks
Gareth
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~|
ColdFusion MX7 by Adobe®
Dyncamically transform webcontent into Adobe PDF with new ColdFusion MX7.
Free Trial. http://www.adobe.com/products/coldfusion?sdid=RVJV
Archive:
http://www.houseoffusion.com/groups/CF-Talk/message.cfm/messageid:278575
Subscription: http://www.houseoffusion.com/groups/CF-Talk/subscribe.cfm
Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=89.70.4
