SQL sum question
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()),'mmdd')#
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
Re: SQL sum question
I really should have been able to figure that out. Hopefully I'll remember next time :) Many thanks Jochem. - Original Message - From: Jochem van Dieten [EMAIL PROTECTED] To: CF-Talk cf-talk@houseoffusion.com Sent: Friday, May 18, 2007 12:36 PM Subject: Re: SQL sum question Gareth Hughes wrote: select pname,sum(pagehit) as pagehit where dates between x and y group by pname, pagehit order by pagehit desc, pname select pname,sum(pagehit) as pageHitCumul where dates between x and y group by pname order by sum(pagehit) desc, pname Jochem ~| Create robust enterprise, web RIAs. Upgrade integrate Adobe Coldfusion MX7 with Flex 2 http://www.adobe.com/products/coldfusion/flex2/?sdid=RVJP Archive: http://www.houseoffusion.com/groups/CF-Talk/message.cfm/messageid:278578 Subscription: http://www.houseoffusion.com/groups/CF-Talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=89.70.4
Re: SQL sum question
Gareth Hughes wrote: select pname,sum(pagehit) as pagehit where dates between x and y group by pname, pagehit order by pagehit desc, pname select pname,sum(pagehit) as pageHitCumul where dates between x and y group by pname order by sum(pagehit) desc, pname Jochem ~| Macromedia ColdFusion MX7 Upgrade to MX7 experience time-saving features, more productivity. http://www.adobe.com/products/coldfusion?sdid=RVJW Archive: http://www.houseoffusion.com/groups/CF-Talk/message.cfm/messageid:278576 Subscription: http://www.houseoffusion.com/groups/CF-Talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=89.70.4
Re: Quick SQL Sum Question
To use SUM( ) the column in question MUST be numeric type (int, decimal, float etc). It doesn't have to be - this works (as long as all the rows can be cast) - select sum (cast(Value as decimal)) as val Regards, Alan Ford ~| Archives: http://www.houseoffusion.com/cf_lists/index.cfm?forumid=4 Subscription: http://www.houseoffusion.com/cf_lists/index.cfm?method=subscribeforumid=4 FAQ: http://www.thenetprofits.co.uk/coldfusion/faq Your ad could be here. Monies from ads go to support these lists and provide more resources for the community. http://www.fusionauthority.com/ads.cfm Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=89.70.4
Quick SQL Sum Question
How can I easily nab the SUM of the inventory.service_shipping records returned in this query via SQL? SUM blah AS cblah tosses an error, I suppose because just one part is aggregate? Any quick ideas? cfquery name=get_productHOLDS datasource=sddheinz SELECT customer_orders_prod_shiphold.product_id, customer_orders_prod_shiphold.qty, customer_orders_prod_shiphold.line_total, customer_orders_prod_shiphold.order_id, inventory.service_name, inventory.service_shipping, inventory.service_desc FROM customer_orders_prod_shiphold LEFT OUTER JOIN inventory on customer_orders_prod_shiphold.product_id = inventory.service_id WHERE customer_orders_prod_shiphold.order_id = cfqueryparam cfsqltype=cf_sql_integer value=#order_id# /cfquery Thanks! Regards, Eric J. Hoffman DataStream Connexion www.datastreamconnexion.com ~| Archives: http://www.houseoffusion.com/cf_lists/index.cfm?forumid=4 Subscription: http://www.houseoffusion.com/cf_lists/index.cfm?method=subscribeforumid=4 FAQ: http://www.thenetprofits.co.uk/coldfusion/faq This list and all House of Fusion resources hosted by CFHosting.com. The place for dependable ColdFusion Hosting. http://www.cfhosting.com Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=89.70.4
RE: Quick SQL Sum Question
To use SUM( ) the column in question MUST be numeric type (int, decimal, float etc). Are you sure you are not trying to get a count rather than a sum? A sum totals all the columns together while a count (select count(blah) as cBlah) tells you the number of rows. -Mark -Original Message- From: Andrew Golden [mailto:[EMAIL PROTECTED] Sent: Wednesday, August 13, 2003 8:27 AM To: CF-Talk Subject: Re: Quick SQL Sum Question At 03:27 PM 8/12/2003 -0500, you wrote: How can I easily nab the SUM of the inventory.service_shipping records returned in this query via SQL? SUM blah AS cblah tosses an error, I suppose because just one part is aggregate? Any quick ideas? Have you tried SUM(blah) AS cblah? Sometimes odd little syntax errors have driven me crazy for hours on end because they have worked differently before...Just a thought. One other option(albeit not as efficient) is to set a variable and loop through the query...I.E. cfquery name=get_productHOLDS datasource=sddheinz SELECT customer_orders_prod_shiphold.product_id, customer_orders_prod_shiphold.qty, customer_orders_prod_shiphold.line_total, customer_orders_prod_shiphold.order_id, inventory.service_name, inventory.service_shipping, inventory.service_desc FROM customer_orders_prod_shiphold LEFT OUTER JOIN inventory on customer_orders_prod_shiphold.product_id = inventory.service_id WHERE customer_orders_prod_shiphold.order_id = cfqueryparam cfsqltype=cf_sql_integer value=#order_id# /cfquery cfset sum = 0 cfloop query=get_productHOLDS cfset sum = sum + service_shipping /cfloop Good luck! Andrew Golden ~| Archives: http://www.houseoffusion.com/cf_lists/index.cfm?forumid=4 Subscription: http://www.houseoffusion.com/cf_lists/index.cfm?method=subscribeforumid=4 FAQ: http://www.thenetprofits.co.uk/coldfusion/faq Your ad could be here. Monies from ads go to support these lists and provide more resources for the community. http://www.fusionauthority.com/ads.cfm Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=89.70.4
RE: Quick SQL Sum Question
Right... but you are actually converting TO a numeric type at runtime. This is done PRIOR to the SUM function. Ergo - sum( ) cannot handle a char datatype. -Original Message- From: Alan Ford [mailto:[EMAIL PROTECTED] Sent: Wednesday, August 13, 2003 10:23 AM To: CF-Talk Subject: Re: Quick SQL Sum Question To use SUM( ) the column in question MUST be numeric type (int, decimal, float etc). It doesn't have to be - this works (as long as all the rows can be cast) - select sum (cast(Value as decimal)) as val Regards, Alan Ford ~| Archives: http://www.houseoffusion.com/cf_lists/index.cfm?forumid=4 Subscription: http://www.houseoffusion.com/cf_lists/index.cfm?method=subscribeforumid=4 FAQ: http://www.thenetprofits.co.uk/coldfusion/faq Signup for the Fusion Authority news alert and keep up with the latest news in ColdFusion and related topics. http://www.fusionauthority.com/signup.cfm Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=89.70.4
Re: Quick SQL Sum Question
At 03:27 PM 8/12/2003 -0500, you wrote: How can I easily nab the SUM of the inventory.service_shipping records returned in this query via SQL? SUM blah AS cblah tosses an error, I suppose because just one part is aggregate? Any quick ideas? Have you tried SUM(blah) AS cblah? Sometimes odd little syntax errors have driven me crazy for hours on end because they have worked differently before...Just a thought. One other option(albeit not as efficient) is to set a variable and loop through the query...I.E. cfquery name=get_productHOLDS datasource=sddheinz SELECT customer_orders_prod_shiphold.product_id, customer_orders_prod_shiphold.qty, customer_orders_prod_shiphold.line_total, customer_orders_prod_shiphold.order_id, inventory.service_name, inventory.service_shipping, inventory.service_desc FROM customer_orders_prod_shiphold LEFT OUTER JOIN inventory on customer_orders_prod_shiphold.product_id = inventory.service_id WHERE customer_orders_prod_shiphold.order_id = cfqueryparam cfsqltype=cf_sql_integer value=#order_id# /cfquery cfset sum = 0 cfloop query=get_productHOLDS cfset sum = sum + service_shipping /cfloop Good luck! Andrew Golden ~| Archives: http://www.houseoffusion.com/cf_lists/index.cfm?forumid=4 Subscription: http://www.houseoffusion.com/cf_lists/index.cfm?method=subscribeforumid=4 FAQ: http://www.thenetprofits.co.uk/coldfusion/faq Signup for the Fusion Authority news alert and keep up with the latest news in ColdFusion and related topics. http://www.fusionauthority.com/signup.cfm Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=89.70.4
RE: Quick SQL Sum Question
Can you post your attempt at using SUM( )? -Original Message- From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED] Sent: Wednesday, August 13, 2003 2:06 PM To: CF-Talk Subject: RE: Quick SQL Sum Question I need the sum...I am trying to get the total of additional shipping from the products being returned. It is a money datatype, so should be fine. But I can't use SUM() alone... Eric -Original Message- From: Mark A. Kruger - CFG [mailto:[EMAIL PROTECTED] Sent: Wednesday, August 13, 2003 8:45 AM To: CF-Talk To use SUM( ) the column in question MUST be numeric type (int, decimal, float etc). Are you sure you are not trying to get a count rather than a sum? A sum totals all the columns together while a count (select count(blah) as cBlah) tells you the number of rows. -Mark -Original Message- From: Andrew Golden [mailto:[EMAIL PROTECTED] Sent: Wednesday, August 13, 2003 8:27 AM To: CF-Talk Subject: Re: Quick SQL Sum Question At 03:27 PM 8/12/2003 -0500, you wrote: How can I easily nab the SUM of the inventory.service_shipping records returned in this query via SQL? SUM blah AS cblah tosses an error, I suppose because just one part is aggregate? Any quick ideas? Have you tried SUM(blah) AS cblah? Sometimes odd little syntax errors have driven me crazy for hours on end because they have worked differently before...Just a thought. One other option(albeit not as efficient) is to set a variable and loop through the query...I.E. cfquery name=get_productHOLDS datasource=sddheinz SELECT customer_orders_prod_shiphold.product_id, customer_orders_prod_shiphold.qty, customer_orders_prod_shiphold.line_total, customer_orders_prod_shiphold.order_id, inventory.service_name, inventory.service_shipping, inventory.service_desc FROM customer_orders_prod_shiphold LEFT OUTER JOIN inventory on customer_orders_prod_shiphold.product_id = inventory.service_id WHERE customer_orders_prod_shiphold.order_id = cfqueryparam cfsqltype=cf_sql_integer value=#order_id# /cfquery cfset sum = 0 cfloop query=get_productHOLDS cfset sum = sum + service_shipping /cfloop Good luck! Andrew Golden ~| Archives: http://www.houseoffusion.com/cf_lists/index.cfm?forumid=4 Subscription: http://www.houseoffusion.com/cf_lists/index.cfm?method=subscribeforumid=4 FAQ: http://www.thenetprofits.co.uk/coldfusion/faq Signup for the Fusion Authority news alert and keep up with the latest news in ColdFusion and related topics. http://www.fusionauthority.com/signup.cfm Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=89.70.4
RE: Quick SQL Sum Question
I need the sum...I am trying to get the total of additional shipping from the products being returned. It is a money datatype, so should be fine. But I can't use SUM() alone... Eric -Original Message- From: Mark A. Kruger - CFG [mailto:[EMAIL PROTECTED] Sent: Wednesday, August 13, 2003 8:45 AM To: CF-Talk To use SUM( ) the column in question MUST be numeric type (int, decimal, float etc). Are you sure you are not trying to get a count rather than a sum? A sum totals all the columns together while a count (select count(blah) as cBlah) tells you the number of rows. -Mark -Original Message- From: Andrew Golden [mailto:[EMAIL PROTECTED] Sent: Wednesday, August 13, 2003 8:27 AM To: CF-Talk Subject: Re: Quick SQL Sum Question At 03:27 PM 8/12/2003 -0500, you wrote: How can I easily nab the SUM of the inventory.service_shipping records returned in this query via SQL? SUM blah AS cblah tosses an error, I suppose because just one part is aggregate? Any quick ideas? Have you tried SUM(blah) AS cblah? Sometimes odd little syntax errors have driven me crazy for hours on end because they have worked differently before...Just a thought. One other option(albeit not as efficient) is to set a variable and loop through the query...I.E. cfquery name=get_productHOLDS datasource=sddheinz SELECT customer_orders_prod_shiphold.product_id, customer_orders_prod_shiphold.qty, customer_orders_prod_shiphold.line_total, customer_orders_prod_shiphold.order_id, inventory.service_name, inventory.service_shipping, inventory.service_desc FROM customer_orders_prod_shiphold LEFT OUTER JOIN inventory on customer_orders_prod_shiphold.product_id = inventory.service_id WHERE customer_orders_prod_shiphold.order_id = cfqueryparam cfsqltype=cf_sql_integer value=#order_id# /cfquery cfset sum = 0 cfloop query=get_productHOLDS cfset sum = sum + service_shipping /cfloop Good luck! Andrew Golden ~| Archives: http://www.houseoffusion.com/cf_lists/index.cfm?forumid=4 Subscription: http://www.houseoffusion.com/cf_lists/index.cfm?method=subscribeforumid=4 FAQ: http://www.thenetprofits.co.uk/coldfusion/faq Signup for the Fusion Authority news alert and keep up with the latest news in ColdFusion and related topics. http://www.fusionauthority.com/signup.cfm Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=89.70.4
RE: SQL SUM Question
Just to add a comment to what everyone else has said - I've always seen different syntax on this. select SUM(Number) "NUMBER" from Tbl_Numbers May be an Oraclism or just local teaching. I'm not that well versed in the differences between MSSQL and Oracle SQL. Brian -Original Message- From: Kevin Queen [mailto:[EMAIL PROTECTED]] Sent: Wednesday, January 03, 2001 4:36 PM To: CF-Talk Subject: SQL SUM Question List, If I run the following query: Select Number from Tbl_Numbers SUM(Number) how can I oputput the SUMed variable in a cfoutput? TIA, Kevin Queen ~~ Structure your ColdFusion code with Fusebox. Get the official book at http://www.fusionauthority.com/bkinfo.cfm Archives: http://www.mail-archive.com/cf-talk@houseoffusion.com/ Unsubscribe: http://www.houseoffusion.com/index.cfm?sidebar=lists
SQL SUM Question
List, If I run the following query: Select Number from Tbl_Numbers SUM(Number) how can I oputput the SUMed variable in a cfoutput? TIA, Kevin Queen ~~ Structure your ColdFusion code with Fusebox. Get the official book at http://www.fusionauthority.com/bkinfo.cfm Archives: http://www.mail-archive.com/cf-talk@houseoffusion.com/ Unsubscribe: http://www.houseoffusion.com/index.cfm?sidebar=lists
RE: SQL SUM Question
Select Number from Tbl_Numbers SUM(Number) AS MyVar cfoutput#MyVar#/Cfoutput -Original Message- From: Kevin Queen [mailto:[EMAIL PROTECTED]] Sent: Wednesday, January 03, 2001 4:36 PM To: CF-Talk Subject: SQL SUM Question List, If I run the following query: Select Number from Tbl_Numbers SUM(Number) how can I oputput the SUMed variable in a cfoutput? TIA, Kevin Queen ~~ Structure your ColdFusion code with Fusebox. Get the official book at http://www.fusionauthority.com/bkinfo.cfm Archives: http://www.mail-archive.com/cf-talk@houseoffusion.com/ Unsubscribe: http://www.houseoffusion.com/index.cfm?sidebar=lists
Re: SQL SUM Question
Wouldn't that be: cfquery datasource=#variable.ds# name="get_sum" select sum(number) as whatever from Tbl_Numbers /cfquery and then cfoutput#get_sum.whatever#/cfoutput ? - Original Message - From: "Kevin Queen" [EMAIL PROTECTED] To: "CF-Talk" [EMAIL PROTECTED] Sent: Wednesday, January 03, 2001 3:36 PM Subject: SQL SUM Question List, If I run the following query: Select Number from Tbl_Numbers SUM(Number) how can I oputput the SUMed variable in a cfoutput? TIA, Kevin Queen ~~ Structure your ColdFusion code with Fusebox. Get the official book at http://www.fusionauthority.com/bkinfo.cfm Archives: http://www.mail-archive.com/cf-talk@houseoffusion.com/ Unsubscribe: http://www.houseoffusion.com/index.cfm?sidebar=lists
RE: SQL SUM Question
You have to select it as something, for example: select sum(number) as sum_number from Tbl_Numbers Regards, Lisa Haas The Kennedy Center -Original Message- From: Kevin Queen [mailto:[EMAIL PROTECTED]] Sent: Wednesday, January 03, 2001 4:36 PM To: CF-Talk Subject: SQL SUM Question List, If I run the following query: Select Number from Tbl_Numbers SUM(Number) how can I oputput the SUMed variable in a cfoutput? TIA, Kevin Queen ~~ Structure your ColdFusion code with Fusebox. Get the official book at http://www.fusionauthority.com/bkinfo.cfm Archives: http://www.mail-archive.com/cf-talk@houseoffusion.com/ Unsubscribe: http://www.houseoffusion.com/index.cfm?sidebar=lists
RE: SQL SUM Question
select number sum(number) from tbl_numbers cfoutput query #number# I think that is right I would try it -Original Message- From: Kevin Queen [mailto:[EMAIL PROTECTED]] Sent: Wednesday, January 03, 2001 4:36 PM To: CF-Talk Subject: SQL SUM Question List, If I run the following query: Select Number from Tbl_Numbers SUM(Number) how can I oputput the SUMed variable in a cfoutput? TIA, Kevin Queen ~~ Structure your ColdFusion code with Fusebox. Get the official book at http://www.fusionauthority.com/bkinfo.cfm Archives: http://www.mail-archive.com/cf-talk@houseoffusion.com/ Unsubscribe: http://www.houseoffusion.com/index.cfm?sidebar=lists
RE: SQL SUM Question
Thats right I gorgot the as -Original Message- From: Kurt Ward [mailto:[EMAIL PROTECTED]] Sent: Wednesday, January 03, 2001 4:40 PM To: CF-Talk Subject: RE: SQL SUM Question Select Number from Tbl_Numbers SUM(Number) AS MyVar cfoutput#MyVar#/Cfoutput -Original Message- From: Kevin Queen [mailto:[EMAIL PROTECTED]] Sent: Wednesday, January 03, 2001 4:36 PM To: CF-Talk Subject: SQL SUM Question List, If I run the following query: Select Number from Tbl_Numbers SUM(Number) how can I oputput the SUMed variable in a cfoutput? TIA, Kevin Queen ~~ Structure your ColdFusion code with Fusebox. Get the official book at http://www.fusionauthority.com/bkinfo.cfm Archives: http://www.mail-archive.com/cf-talk@houseoffusion.com/ Unsubscribe: http://www.houseoffusion.com/index.cfm?sidebar=lists
