Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

 All Forums
 SQL Server 2012 Forums
 Transact-SQL (2012)
 Monthly average

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 - 1205
February - 2054
March - 1544
April - 2354
May - 2003
June - 2210

Then the average monthly total would be:
(1205 + 2054 + 1544 + 2354 + 2003 + 2210) / 6

So that is the result I'm looking for - one figure indicating the average monthly total for a selected date range

Any 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 MonthlyAverage
FROM
(
SELECT SUM(IntegerField) AS Total
FROM Table
WHERE datefield >=@StartDate
AND datefield < DATEADD(dd,1,@EndDate)
)t
[/code]

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

Go to Top of Page

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 MonthlyAverage
FROM
(
SELECT SUM(IntegerField) AS Total
FROM Table
WHERE datefield >=@StartDate
AND datefield < DATEADD(dd,1,@EndDate)
)t


------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/






Too old to Rock'n'Roll too young to die.
Go to Top of Page

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 MonthlyAverage
FROM
(
SELECT SUM(IntegerField) AS Total
FROM Table
WHERE datefield >=@StartDate
AND datefield < DATEADD(dd,1,@EndDate)
)t


------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/






Too old to Rock'n'Roll too young to die.


from OPs statement
So that is the result I'm looking for - one figure indicating the average monthly total for a selected date range

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

Go to Top of Page

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 MonthlyAverage
FROM
(
SELECT SUM(IntegerField) AS Total
FROM Table
WHERE datefield >=@StartDate
AND datefield < DATEADD(dd,1,@EndDate)
)t


------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/






Too old to Rock'n'Roll too young to die.


from OPs statement
So that is the result I'm looking for - one figure indicating the average monthly total for a selected date range

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/






Too old to Rock'n'Roll too young to die.
Go to Top of Page

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 @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.
Go to Top of Page

wholesalenfljerseyssale
Starting Member

9 Posts

Posted - 2013-01-11 : 01:11:57
unspammed
Go to Top of Page

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."
Go to Top of Page

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



see

http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=128923&whichpage=40

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

Go to Top of Page
   

- Advertisement -