SQL Server Forums Profile | Register | Active Topics | Members | Search | Forum FAQ Register Now and get your question answered!
 All Forums  SQL Server 2012 Forums  Transact-SQL (2012)  Monthly average New Topic  Reply to Topic  Printer Friendly
Author  Topic

Fabricio
Starting Member

South Africa
1 Posts

 Posted - 01/10/2013 :  03:34:22 I am struggling with the following query for Microsoft SQL Server 2012:I have a table with a date and an integer column. I need to calculate the monthly average sum of the integer column in a given date range. In other words, if i select 1 January to 31 June, I need to know what the average monthly total was. So, for example:January - 1205February - 2054March - 1544April - 2354May - 2003June - 2210Then the average monthly total would be:(1205 + 2054 + 1544 + 2354 + 2003 + 2210) / 6So that is the result I'm looking for - one figure indicating the average monthly total for a selected date rangeAny help would be greatly appreciated. Thank you..

visakh16
Very Important crosS Applying yaK Herder

India
47173 Posts

 Posted - 01/10/2013 :  03:36:45 ``` SELECT AVG(Total*1.0) AS MonthlyAverage FROM ( SELECT SUM(IntegerField) AS Total FROM Table WHERE datefield >=@StartDate AND datefield < DATEADD(dd,1,@EndDate) )t ```------------------------------------------------------------------------------------------------------SQL Server MVPhttp://visakhm.blogspot.com/

webfred
Flowing Fount of Yak Knowledge

Germany
8515 Posts

 Posted - 01/10/2013 :  04:14:17 No GROUP BY Month?quote:Originally posted by visakh16``` SELECT AVG(Total*1.0) AS MonthlyAverage FROM ( SELECT SUM(IntegerField) AS Total FROM Table WHERE datefield >=@StartDate AND datefield < DATEADD(dd,1,@EndDate) )t ```------------------------------------------------------------------------------------------------------SQL Server MVPhttp://visakhm.blogspot.com/Too old to Rock'n'Roll too young to die.

visakh16
Very Important crosS Applying yaK Herder

India
47173 Posts

 Posted - 01/10/2013 :  04:16:26 quote:Originally posted by webfredNo GROUP BY Month?quote:Originally posted by visakh16``` SELECT AVG(Total*1.0) AS MonthlyAverage FROM ( SELECT SUM(IntegerField) AS Total FROM Table WHERE datefield >=@StartDate AND datefield < DATEADD(dd,1,@EndDate) )t ```------------------------------------------------------------------------------------------------------SQL Server MVPhttp://visakhm.blogspot.com/Too old to Rock'n'Roll too young to die.from OPs statementSo that is the result I'm looking for - one figure indicating the average monthly total for a selected date range------------------------------------------------------------------------------------------------------SQL Server MVPhttp://visakhm.blogspot.com/

webfred
Flowing Fount of Yak Knowledge

Germany
8515 Posts

 Posted - 01/10/2013 :  05:05:34 Ah, ok quote:Originally posted by visakh16quote:Originally posted by webfredNo GROUP BY Month?quote:Originally posted by visakh16``` SELECT AVG(Total*1.0) AS MonthlyAverage FROM ( SELECT SUM(IntegerField) AS Total FROM Table WHERE datefield >=@StartDate AND datefield < DATEADD(dd,1,@EndDate) )t ```------------------------------------------------------------------------------------------------------SQL Server MVPhttp://visakhm.blogspot.com/Too old to Rock'n'Roll too young to die.from OPs statementSo that is the result I'm looking for - one figure indicating the average monthly total for a selected date range------------------------------------------------------------------------------------------------------SQL Server MVPhttp://visakhm.blogspot.com/Too old to Rock'n'Roll too young to die.

webfred
Flowing Fount of Yak Knowledge

Germany
8515 Posts

 Posted - 01/10/2013 :  05:14:10 No, I think it is not ok.Maybe this:```declare @sample table(theDate datetime, theValue int) insert @sample select '20130120', 10 union all select '20130122', 30 union all select '20130215', 40 union all select '20130216', 100 SELECT AVG(Total * 1.0) AS MonthlyAverage FROM ( SELECT SUM(theValue) AS Total FROM @sample WHERE theDate >='20130101' AND theDate < DATEADD(dd,1,'20130630') group by month(theDate) )t ```Too old to Rock'n'Roll too young to die.

wholesalenfljerseyssale
Starting Member

9 Posts

Jeff Moden
Aged Yak Warrior

USA
643 Posts

 Posted - 01/14/2013 :  23:40:06 How do you report SPAM on this forum?--Jeff ModenRBAR is pronounced "ree-bar" and is a "Modenism" for "Row By Agonizing Row".First step towards the paradigm shift of writing Set Based code:"Stop thinking about what you want to do to a row... think, instead, of what you want to do to a column." When writing schedules, keep the following in mind:"If you want it real bad, that's the way you'll likely get it."

visakh16
Very Important crosS Applying yaK Herder

India
47173 Posts

 Posted - 01/14/2013 :  23:47:41 quote:Originally posted by Jeff ModenHow do you report SPAM on this forum?--Jeff ModenRBAR is pronounced "ree-bar" and is a "Modenism" for "Row By Agonizing Row".First step towards the paradigm shift of writing Set Based code:"Stop thinking about what you want to do to a row... think, instead, of what you want to do to a column." When writing schedules, keep the following in mind:"If you want it real bad, that's the way you'll likely get it."seehttp://www.sqlteam.com/forums/topic.asp?TOPIC_ID=128923&whichpage=40------------------------------------------------------------------------------------------------------SQL Server MVPhttp://visakhm.blogspot.com/
Topic
 New Topic  Reply to Topic  Printer Friendly Jump To: Select Forum General SQL Server Forums       New to SQL Server Programming       New to SQL Server Administration       Script Library       Data Corruption Issues       Database Design and Application Architecture SQL Server 2012 Forums       Transact-SQL (2012)       SQL Server Administration (2012)       SSIS and Import/Export (2012)       Analysis Server and Reporting Services (2012)       Replication (2012)       Availability Groups and DR (2012)       Other SQL Server 2012 Topics SQL Server 2008 Forums       Transact-SQL (2008)       SQL Server Administration (2008)       SSIS and Import/Export (2008)       High Availability (2008)       Replication (2008)       Analysis Server and Reporting Services (2008)       Other SQL Server 2008 Topics SQL Server 2005 Forums       Transact-SQL (2005)       SQL Server Administration (2005)       .NET Inside SQL Server (2005)       SSIS and Import/Export (2005)       Service Broker (2005)       Replication (2005)       High Availability (2005)       Analysis Server and Reporting Services (2005)       Express Edition and Compact Edition (2005)       Other SQL Server Topics (2005) SQL Server 2000 Forums       SQL Server Development (2000)       SQL Server Administration (2000)       Import/Export (DTS) and Replication (2000)       Transact-SQL (2000)       Analysis Services (2000)       MSDE (2000) Development Tools       ASP.NET       Reporting Services Development       Other Development Tools Site Related Forums       Site Related Discussions       Article Discussion       Poll Discussion       The Yak Corral Other Forums       SQL Server 6.5 \ SQL Server 7.0       Other Topics       MS Access       ClearTrace Support Forum Old Forums       CLOSED - General SQL Server       CLOSED - SQL Server 2005/Yukon  -------------------- Home Active Topics Frequently Asked Questions Member Information Search Page
 SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC