Author |
Topic |
Fabricio
Starting Member
3 Posts |
Posted - 2013-01-10 : 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
52326 Posts |
Posted - 2013-01-10 : 03:36:45
|
[code]SELECT AVG(Total*1.0) AS MonthlyAverageFROM(SELECT SUM(IntegerField) AS TotalFROM TableWHERE datefield >=@StartDateAND datefield < DATEADD(dd,1,@EndDate))t[/code]------------------------------------------------------------------------------------------------------SQL Server MVPhttp://visakhm.blogspot.com/ |
|
|
webfred
Master Smack Fu Yak Hacker
8781 Posts |
Posted - 2013-01-10 : 04:14:17
|
No GROUP BY Month?quote: Originally posted by visakh16
SELECT AVG(Total*1.0) AS MonthlyAverageFROM(SELECT SUM(IntegerField) AS TotalFROM TableWHERE datefield >=@StartDateAND 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
52326 Posts |
Posted - 2013-01-10 : 04:16:26
|
quote: Originally posted by webfred No GROUP BY Month?quote: Originally posted by visakh16
SELECT AVG(Total*1.0) AS MonthlyAverageFROM(SELECT SUM(IntegerField) AS TotalFROM TableWHERE datefield >=@StartDateAND 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
Master Smack Fu Yak Hacker
8781 Posts |
Posted - 2013-01-10 : 05:05:34
|
Ah, ok quote: Originally posted by visakh16
quote: Originally posted by webfred No GROUP BY Month?quote: Originally posted by visakh16
SELECT AVG(Total*1.0) AS MonthlyAverageFROM(SELECT SUM(IntegerField) AS TotalFROM TableWHERE datefield >=@StartDateAND 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
Master Smack Fu Yak Hacker
8781 Posts |
Posted - 2013-01-10 : 05:14:10
|
No, I think it is not ok.Maybe this:declare @sample table(theDate datetime, theValue int)insert @sampleselect '20130120', 10 union allselect '20130122', 30 union allselect '20130215', 40 union allselect '20130216', 100SELECT AVG(Total * 1.0) AS MonthlyAverageFROM(SELECT SUM(theValue) AS TotalFROM @sampleWHERE 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 |
Posted - 2013-01-11 : 01:11:57
|
unspammed |
|
|
Jeff Moden
Aged Yak Warrior
652 Posts |
Posted - 2013-01-14 : 23:40:06
|
How do you report SPAM on this forum?--Jeff Moden RBAR 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
52326 Posts |
Posted - 2013-01-14 : 23:47:41
|
quote: Originally posted by Jeff Moden How do you report SPAM on this forum?--Jeff Moden RBAR 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/ |
|
|
|