| 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 - 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
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 MVP
http://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 MVP
http://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 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/
|
|
|
|
webfred
Flowing Fount of Yak Knowledge
Germany
8515 Posts |
Posted - 01/10/2013 : 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. |
|
|
|
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 |
Posted - 01/11/2013 : 01:11:57
|
Cheap Jerseys from China online store
The NFL Jerseys Is National Football League (NFL) Cheap Jerseys is the highest level of professional American football in the NFL Replica Jerseys United States. It was formed by eleven teams in 1920 as the American Football Jerseys Wholesale Professional Football Association, with the league changing its name to the National Football League in 1922 Cheap NFL Jerseys. The league is divided evenly into two conferences -- the American Football Reebok NFL Jerseys Conference (AFC) and National Football Cheap Jerseys USAConference (NFC), and each conference has four divisions that have four teams each, for a total of 16 teams in each conference. The Replica NFL Jerseys is an unincorporated 501(c)(6) association, Cheap NFL Jerseys a federal nonprofit designation,NFL Youth Jerseys comprising its 32 teams.
National Hockey League begins with the demise of its predecessor league, Replica NHL Jerseys the National Hockey Association (NHA), in 1917. NHL Jerseys After unsuccessfully resolving disputes with Eddie Livingstone, owner of the Toronto Blueshirts, executives Cheap Jerseys of the three other NHA franchises suspended the NHA, and formed the National Hockey League (NHL), replacing the Livingstone team Cheap NHL Jerseys with a temporary team in Toronto, the Arenas. The NHL's first quarter-century saw the league compete against two rival major leagues--the Pacific Coast Hockey Association and Western Canada Hockey League--for players and the Stanley Cup. The Reebok NHL Jerseys first expanded into the United States in 1924 with the founding of the Boston Bruins, and by 1926 consisted of ten teams in Ontario, Quebec, the Great Lakes region, and the Northeastern NHL Kids Jerseys United States. At the same time, the NHL And NFL Women Jerseys emerged as the only major league and the sole competitor for the Stanley Cup; in 1947, the NHL completed a deal with the Stanley Cup trustees to gain full control of the Cup. The NHL's footprint spread across Canada as Wholesale Jerseys Foster Hewitt's radio broadcasts were heard coast-to-coast starting in 1933.
Baseball has emerged from lots of cheap authentic jerserys ups and down. From the era of dead ball to the modern era, we have seen so many great players reviving this game every year. Until 1920's baseball used to have era known as The authentic jerseys Dead Ball Era. During this time teams use to use same ball for whole game. The ball used to get softened and fewer home runs were scored that era. Cheap jerseys Hitters relied mainly on base hits, single or double for scoring. Since the teams used to use one ball for entire game the people never saw lot of home runs during this era. Baseball saw geat hitter MLB Jerseys and pitchers during that era. Honus Wagner, Ty Cobb played exceptionally at that time and gave hitting a new life. Cy young, Walter Johnson proved that even the ball is dead after few innings they can still pitch the ball where ever they want to. These greats laid the foundation of baseball Cheap MLB Jerseys which we see today in all its glory. World Series started during this period. It was in 1920 when baseball was going through the scandals that Boston Red Sox sent Babe Ruth to New York Yankees for $125,000. And that was a start of the a exciting era for baseball. Babe Ruth played like a hero in Yankees and became one the best player in Baseball. Wholesale Jerseys He hit 54 home runs in his first season in Yankees. During this period of baseball more ball were used during the game. The ball was hard and whiter whole of the game. This helped hitters to score more runs and it made baseball more interesting for people. During this era we saw hitters like Babe Ruth, Lou Gehrig, and Hank Greenberg. After world was II a new era started for baseball.
The National Basketball Association (NBA) is NBA Jerseys the pre-eminent men's professional basketball Cheap Jerseys league in North America. It consists of thirty franchised member clubs, of which twenty-nine are located in the United States and one in Canada. It is an active member of USA Basketball Replica Jerseys Was (USAB),[1] which is recognized by the International Basketball Federation as the National Governing Body (NGB) for basketball in the United States.
Attention Clearance Sale fans! Use the Clearance Sale Categories below to purchase Clearance Sale Apparel, Clearance Sale Gifts and Clearance Sale Merchandise. We are your Jerseys for sale for all the authentic Clearance Sale Gear. Because you are a Clearance Sale fanatic you should have only the best licensed Clearance Sale Apparel guaranteed to show your Clearance Sale Pride. Fanzz only carries licensed Clearance Sale Jerseys, Clearance Sale T-Shirts, Clearance Sale Hats and Clearance Sale Drinkwar |
|
|
|
Jeff Moden
Aged Yak Warrior
USA
643 Posts |
Posted - 01/14/2013 : 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
India
47173 Posts |
Posted - 01/14/2013 : 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/
|
|
|
| |
Topic |
|
Monthly average
