SQL Server Forums
Profile | Register | 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
 New Topic  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
52325 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
8768 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
52325 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
8768 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
8768 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
52325 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  
 New 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.08 seconds. Powered By: Snitz Forums 2000