Averaging values by month

Given a series of dates with values I want to find the average by averaging
the data by month and then taking the average of these values. I can do this
(see below) by creating an additional temp table but I'd like to do this
with a single SELECT. Thanks.
CREATE TABLE #TestTable
(
TestDate datetime,
TestValue decimal(18,4)
)
INSERT INTO #TestTable (TestDate,TestValue)VALUES ('20060101',
63138498.9442)
INSERT INTO #TestTable (TestDate,TestValue)VALUES ('20060102', 6018866.7861)
INSERT INTO #TestTable (TestDate,TestValue)VALUES ('20060103',
32423405.9143)
INSERT INTO #TestTable (TestDate,TestValue)VALUES ('20060104',
51402184.2973)
INSERT INTO #TestTable (TestDate,TestValue)VALUES ('20060105',
41984683.2967)
INSERT INTO #TestTable (TestDate,TestValue)VALUES ('20060106',
52707356.8602)
INSERT INTO #TestTable (TestDate,TestValue)VALUES ('20060107',
60084423.5696)
INSERT INTO #TestTable (TestDate,TestValue)VALUES ('20060108',
10256576.8087)
INSERT INTO #TestTable (TestDate,TestValue)VALUES ('20060109',
50973538.1018)
INSERT INTO #TestTable (TestDate,TestValue)VALUES ('20060110',
32250398.2328)
INSERT INTO #TestTable (TestDate,TestValue)VALUES ('20060111',
52293744.1444)
INSERT INTO #TestTable (TestDate,TestValue)VALUES ('20060112',
97993941.6661)
INSERT INTO #TestTable (TestDate,TestValue)VALUES ('20060113',
19633802.8665)
INSERT INTO #TestTable (TestDate,TestValue)VALUES ('20060114',
44710651.7875)
INSERT INTO #TestTable (TestDate,TestValue)VALUES ('20060115',
24443905.2030)
INSERT INTO #TestTable (TestDate,TestValue)VALUES ('20060116',
84725698.7918)
INSERT INTO #TestTable (TestDate,TestValue)VALUES ('20060117',
12220534.4395)
INSERT INTO #TestTable (TestDate,TestValue)VALUES ('20060118',
39001430.3432)
INSERT INTO #TestTable (TestDate,TestValue)VALUES ('20060119',
54180100.6619)
INSERT INTO #TestTable (TestDate,TestValue)VALUES ('20060120',
23901300.4697)
INSERT INTO #TestTable (TestDate,TestValue)VALUES ('20060121',
55804613.2757)
INSERT INTO #TestTable (TestDate,TestValue)VALUES ('20060122',
78239680.7124)
INSERT INTO #TestTable (TestDate,TestValue)VALUES ('20060123',
13036976.3434)
INSERT INTO #TestTable (TestDate,TestValue)VALUES ('20060124',
57277368.5493)
INSERT INTO #TestTable (TestDate,TestValue)VALUES ('20060125',
42169222.2497)
INSERT INTO #TestTable (TestDate,TestValue)VALUES ('20060126',
65064414.7238)
INSERT INTO #TestTable (TestDate,TestValue)VALUES ('20060127',
18749702.5770)
INSERT INTO #TestTable (TestDate,TestValue)VALUES ('20060128',
61961708.5242)
INSERT INTO #TestTable (TestDate,TestValue)VALUES ('20060129',
47072115.8125)
INSERT INTO #TestTable (TestDate,TestValue)VALUES ('20060130',
16382094.7146)
INSERT INTO #TestTable (TestDate,TestValue)VALUES ('20060131', 9684892.4976)
INSERT INTO #TestTable (TestDate,TestValue)VALUES ('20060201',
36461918.8525)
INSERT INTO #TestTable (TestDate,TestValue)VALUES ('20060202',
13637750.0447)
INSERT INTO #TestTable (TestDate,TestValue)VALUES ('20060203',
57475888.7342)
INSERT INTO #TestTable (TestDate,TestValue)VALUES ('20060204',
91835958.7101)
INSERT INTO #TestTable (TestDate,TestValue)VALUES ('20060205',
42083192.0164)
INSERT INTO #TestTable (TestDate,TestValue)VALUES ('20060206',
20161492.7101)
INSERT INTO #TestTable (TestDate,TestValue)VALUES ('20060207',
27152605.8613)
INSERT INTO #TestTable (TestDate,TestValue)VALUES ('20060208',
86874863.6639)
INSERT INTO #TestTable (TestDate,TestValue)VALUES ('20060209',
19168743.2577)
INSERT INTO #TestTable (TestDate,TestValue)VALUES ('20060210',
77360233.5777)
INSERT INTO #TestTable (TestDate,TestValue)VALUES ('20060211',
75986666.4099)
INSERT INTO #TestTable (TestDate,TestValue)VALUES ('20060212',
86183511.9510)
INSERT INTO #TestTable (TestDate,TestValue)VALUES ('20060213',
29403571.0172)
INSERT INTO #TestTable (TestDate,TestValue)VALUES ('20060214',
93603660.1728)
INSERT INTO #TestTable (TestDate,TestValue)VALUES ('20060215', 2679256.8156)
INSERT INTO #TestTable (TestDate,TestValue)VALUES ('20060216',
34113885.8418)
INSERT INTO #TestTable (TestDate,TestValue)VALUES ('20060217',
53605552.5087)
INSERT INTO #TestTable (TestDate,TestValue)VALUES ('20060218',
81263888.2651)
INSERT INTO #TestTable (TestDate,TestValue)VALUES ('20060219',
13779351.1302)
INSERT INTO #TestTable (TestDate,TestValue)VALUES ('20060220',
48140149.5757)
INSERT INTO #TestTable (TestDate,TestValue)VALUES ('20060221', 5541682.8948)
INSERT INTO #TestTable (TestDate,TestValue)VALUES ('20060222',
46000410.1362)
INSERT INTO #TestTable (TestDate,TestValue)VALUES ('20060223',
91160648.0837)
INSERT INTO #TestTable (TestDate,TestValue)VALUES ('20060224', 9857530.4764)
INSERT INTO #TestTable (TestDate,TestValue)VALUES ('20060225',
31939509.0333)
INSERT INTO #TestTable (TestDate,TestValue)VALUES ('20060226',
99050916.0463)
INSERT INTO #TestTable (TestDate,TestValue)VALUES ('20060227', 179190.5861)
INSERT INTO #TestTable (TestDate,TestValue)VALUES ('20060228',
80963446.2681)
CREATE TABLE #TestResults
(
TestValue decimal(18,4)
)
INSERT INTO #TestResults
SELECT
AVG(TestValue)
FROM #TestTable
GROUP BY Month(testDate)
SELECT AVG(TestValue) FROM #TestResults
DROP TABLE #TestTable
DROP TABLE #TestResultsTerri,
You can use a derived table for this like so:
SELECT AVG(MonthAvg)
FROM (SELECT AVG(TestValue) AS MonthAvg
FROM #TestTable
GROUP BY CONVERT(CHAR(6), TestDate, 112)) AS D;
BG, SQL Server MVP
www.SolidQualityLearning.com
www.insidetsql.com
Anything written in this message represents my view, my own view, and
nothing but my view (WITH SCHEMABINDING), so help me my T-SQL code.
"Terri" <terri@.cybernets.com> wrote in message
news:e3nr55$8d1$1@.reader2.nmix.net...
> Given a series of dates with values I want to find the average by
> averaging
> the data by month and then taking the average of these values. I can do
> this
> (see below) by creating an additional temp table but I'd like to do this
> with a single SELECT. Thanks.
> CREATE TABLE #TestTable
> (
> TestDate datetime,
> TestValue decimal(18,4)
> )
> INSERT INTO #TestTable (TestDate,TestValue)VALUES ('20060101',
> 63138498.9442)
> INSERT INTO #TestTable (TestDate,TestValue)VALUES ('20060102',
> 6018866.7861)
> INSERT INTO #TestTable (TestDate,TestValue)VALUES ('20060103',
> 32423405.9143)
> INSERT INTO #TestTable (TestDate,TestValue)VALUES ('20060104',
> 51402184.2973)
> INSERT INTO #TestTable (TestDate,TestValue)VALUES ('20060105',
> 41984683.2967)
> INSERT INTO #TestTable (TestDate,TestValue)VALUES ('20060106',
> 52707356.8602)
> INSERT INTO #TestTable (TestDate,TestValue)VALUES ('20060107',
> 60084423.5696)
> INSERT INTO #TestTable (TestDate,TestValue)VALUES ('20060108',
> 10256576.8087)
> INSERT INTO #TestTable (TestDate,TestValue)VALUES ('20060109',
> 50973538.1018)
> INSERT INTO #TestTable (TestDate,TestValue)VALUES ('20060110',
> 32250398.2328)
> INSERT INTO #TestTable (TestDate,TestValue)VALUES ('20060111',
> 52293744.1444)
> INSERT INTO #TestTable (TestDate,TestValue)VALUES ('20060112',
> 97993941.6661)
> INSERT INTO #TestTable (TestDate,TestValue)VALUES ('20060113',
> 19633802.8665)
> INSERT INTO #TestTable (TestDate,TestValue)VALUES ('20060114',
> 44710651.7875)
> INSERT INTO #TestTable (TestDate,TestValue)VALUES ('20060115',
> 24443905.2030)
> INSERT INTO #TestTable (TestDate,TestValue)VALUES ('20060116',
> 84725698.7918)
> INSERT INTO #TestTable (TestDate,TestValue)VALUES ('20060117',
> 12220534.4395)
> INSERT INTO #TestTable (TestDate,TestValue)VALUES ('20060118',
> 39001430.3432)
> INSERT INTO #TestTable (TestDate,TestValue)VALUES ('20060119',
> 54180100.6619)
> INSERT INTO #TestTable (TestDate,TestValue)VALUES ('20060120',
> 23901300.4697)
> INSERT INTO #TestTable (TestDate,TestValue)VALUES ('20060121',
> 55804613.2757)
> INSERT INTO #TestTable (TestDate,TestValue)VALUES ('20060122',
> 78239680.7124)
> INSERT INTO #TestTable (TestDate,TestValue)VALUES ('20060123',
> 13036976.3434)
> INSERT INTO #TestTable (TestDate,TestValue)VALUES ('20060124',
> 57277368.5493)
> INSERT INTO #TestTable (TestDate,TestValue)VALUES ('20060125',
> 42169222.2497)
> INSERT INTO #TestTable (TestDate,TestValue)VALUES ('20060126',
> 65064414.7238)
> INSERT INTO #TestTable (TestDate,TestValue)VALUES ('20060127',
> 18749702.5770)
> INSERT INTO #TestTable (TestDate,TestValue)VALUES ('20060128',
> 61961708.5242)
> INSERT INTO #TestTable (TestDate,TestValue)VALUES ('20060129',
> 47072115.8125)
> INSERT INTO #TestTable (TestDate,TestValue)VALUES ('20060130',
> 16382094.7146)
> INSERT INTO #TestTable (TestDate,TestValue)VALUES ('20060131',
> 9684892.4976)
> INSERT INTO #TestTable (TestDate,TestValue)VALUES ('20060201',
> 36461918.8525)
> INSERT INTO #TestTable (TestDate,TestValue)VALUES ('20060202',
> 13637750.0447)
> INSERT INTO #TestTable (TestDate,TestValue)VALUES ('20060203',
> 57475888.7342)
> INSERT INTO #TestTable (TestDate,TestValue)VALUES ('20060204',
> 91835958.7101)
> INSERT INTO #TestTable (TestDate,TestValue)VALUES ('20060205',
> 42083192.0164)
> INSERT INTO #TestTable (TestDate,TestValue)VALUES ('20060206',
> 20161492.7101)
> INSERT INTO #TestTable (TestDate,TestValue)VALUES ('20060207',
> 27152605.8613)
> INSERT INTO #TestTable (TestDate,TestValue)VALUES ('20060208',
> 86874863.6639)
> INSERT INTO #TestTable (TestDate,TestValue)VALUES ('20060209',
> 19168743.2577)
> INSERT INTO #TestTable (TestDate,TestValue)VALUES ('20060210',
> 77360233.5777)
> INSERT INTO #TestTable (TestDate,TestValue)VALUES ('20060211',
> 75986666.4099)
> INSERT INTO #TestTable (TestDate,TestValue)VALUES ('20060212',
> 86183511.9510)
> INSERT INTO #TestTable (TestDate,TestValue)VALUES ('20060213',
> 29403571.0172)
> INSERT INTO #TestTable (TestDate,TestValue)VALUES ('20060214',
> 93603660.1728)
> INSERT INTO #TestTable (TestDate,TestValue)VALUES ('20060215',
> 2679256.8156)
> INSERT INTO #TestTable (TestDate,TestValue)VALUES ('20060216',
> 34113885.8418)
> INSERT INTO #TestTable (TestDate,TestValue)VALUES ('20060217',
> 53605552.5087)
> INSERT INTO #TestTable (TestDate,TestValue)VALUES ('20060218',
> 81263888.2651)
> INSERT INTO #TestTable (TestDate,TestValue)VALUES ('20060219',
> 13779351.1302)
> INSERT INTO #TestTable (TestDate,TestValue)VALUES ('20060220',
> 48140149.5757)
> INSERT INTO #TestTable (TestDate,TestValue)VALUES ('20060221',
> 5541682.8948)
> INSERT INTO #TestTable (TestDate,TestValue)VALUES ('20060222',
> 46000410.1362)
> INSERT INTO #TestTable (TestDate,TestValue)VALUES ('20060223',
> 91160648.0837)
> INSERT INTO #TestTable (TestDate,TestValue)VALUES ('20060224',
> 9857530.4764)
> INSERT INTO #TestTable (TestDate,TestValue)VALUES ('20060225',
> 31939509.0333)
> INSERT INTO #TestTable (TestDate,TestValue)VALUES ('20060226',
> 99050916.0463)
> INSERT INTO #TestTable (TestDate,TestValue)VALUES ('20060227',
> 179190.5861)
> INSERT INTO #TestTable (TestDate,TestValue)VALUES ('20060228',
> 80963446.2681)
> CREATE TABLE #TestResults
> (
> TestValue decimal(18,4)
> )
> INSERT INTO #TestResults
> SELECT
> AVG(TestValue)
> FROM #TestTable
> GROUP BY Month(testDate)
> SELECT AVG(TestValue) FROM #TestResults
>
> DROP TABLE #TestTable
> DROP TABLE #TestResults
>
>