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

Our new SQL Server Forums are live! Come on over! We've restricted the ability to create new threads on these forums.

SQL Server Forums
Profile | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 SQL Server 2012 Forums
 Transact-SQL (2012)
 Monthly average
 Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

Fabricio
Starting Member

South Africa
3 Posts

Posted - 01/10/2013 :  03:34:22  Show Profile  Reply with Quote
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

India
52326 Posts

Posted - 01/10/2013 :  03:36:45  Show Profile  Reply with Quote

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/

Go to Top of Page

webfred
Flowing Fount of Yak Knowledge

Germany
8781 Posts

Posted - 01/10/2013 :  04:14:17  Show Profile  Visit webfred's Homepage  Reply with Quote
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

India
52326 Posts

Posted - 01/10/2013 :  04:16:26  Show Profile  Reply with Quote
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
Flowing Fount of Yak Knowledge

Germany
8781 Posts

Posted - 01/10/2013 :  05:05:34  Show Profile  Visit webfred's Homepage  Reply with Quote
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
Flowing Fount of Yak Knowledge

Germany
8781 Posts

Posted - 01/10/2013 :  05:14:10  Show Profile  Visit webfred's Homepage  Reply with Quote
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 - 01/11/2013 :  01:11:57  Show Profile  Reply with Quote
unspammed
Go to Top of Page

Jeff Moden
Aged Yak Warrior

USA
651 Posts

Posted - 01/14/2013 :  23:40:06  Show Profile  Reply with Quote
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

India
52326 Posts

Posted - 01/14/2013 :  23:47:41  Show Profile  Reply with Quote
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
  Previous Topic Topic Next Topic  
 Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.16 seconds. Powered By: Snitz Forums 2000