AVG of integer column returns integers result - newbie

I am averaging integer columns, the result is integer not real or decimal
How do I get aroung this?
SELECT AVG(AmpReading)
FROM tblChillWaterSystems
GROUP BY LocationID, SystemID
Values are
1
3
6
--
10
10/3 = 3.33 but the result is 3SELECT AVG(CAST(AmpReading AS REAL)) ...
David Portas
SQL Server MVP
--|||Try,
SELECT AVG(AmpReading * 1.00)
FROM tblChillWaterSystems
GROUP BY LocationID, SystemID
or cast [AmpReading] to numeric.
AMB
"Craig" wrote:

> I am averaging integer columns, the result is integer not real or decimal
> How do I get aroung this?
> SELECT AVG(AmpReading)
> FROM tblChillWaterSystems
> GROUP BY LocationID, SystemID
> Values are
> 1
> 3
> 6
> --
> 10
> 10/3 = 3.33 but the result is 3
>
>

AVG of integer column returns integers result - newbie

I am averaging integer columns, the result is integer not real or decimal
How do I get aroung this?
SELECT AVG(AmpReading)
FROM tblChillWaterSystems
GROUP BY LocationID, SystemID
Values are
1
3
6
10
10/3 = 3.33 but the result is 3
SELECT AVG(CAST(AmpReading AS REAL)) ...
David Portas
SQL Server MVP
|||Try,
SELECT AVG(AmpReading * 1.00)
FROM tblChillWaterSystems
GROUP BY LocationID, SystemID
or cast [AmpReading] to numeric.
AMB
"Craig" wrote:

> I am averaging integer columns, the result is integer not real or decimal
> How do I get aroung this?
> SELECT AVG(AmpReading)
> FROM tblChillWaterSystems
> GROUP BY LocationID, SystemID
> Values are
> 1
> 3
> 6
> --
> 10
> 10/3 = 3.33 but the result is 3
>
>

AVG of integer column returns integers result - newbie

I am averaging integer columns, the result is integer not real or decimal
How do I get aroung this?
SELECT AVG(AmpReading)
FROM tblChillWaterSystems
GROUP BY LocationID, SystemID
Values are
1
3
6
--
10
10/3 = 3.33 but the result is 3SELECT AVG(CAST(AmpReading AS REAL)) ...
--
David Portas
SQL Server MVP
--|||Try,
SELECT AVG(AmpReading * 1.00)
FROM tblChillWaterSystems
GROUP BY LocationID, SystemID
or cast [AmpReading] to numeric.
AMB
"Craig" wrote:
> I am averaging integer columns, the result is integer not real or decimal
> How do I get aroung this?
> SELECT AVG(AmpReading)
> FROM tblChillWaterSystems
> GROUP BY LocationID, SystemID
> Values are
> 1
> 3
> 6
> --
> 10
> 10/3 = 3.33 but the result is 3
>
>

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

Averaging the Averages

I need to average the resulting averages from the query below
This query gets the averages for each TowerNumber.
SELECT LocationID, SystemID, TowerNumber, COUNT(TowerNumber) AS
CountEntriesPerTowerNumber, AVG(SupplyCalciumHardness * 1.00)
AS AvgSupplyCalciumHardness
FROM tblTowers
WHERE (ReadingDate BETWEEN @.BeginningDate AND @.EndingDate) AND (SystemID
= @.SystemID) AND
(LocationID = @.LocationID)
GROUP BY LocationID, SystemID, TowerNumber
What I really want is the average of the averages for
AVG(SupplyCalciumHardness * 1.00).
Something like this AVG(AVG(SupplyCalciumHardness * 1.00))
TowerNumber AvgSupplyCalciumHardness
1 14
2 18
3 7
4 8
--
Sum of averages = 47
Avg of Avg = 47 / 4 = 11.75
How can I do this?
ThanksOn Wed, 19 Oct 2005 14:41:55 -0500, "Craig" <NoSpam@.hotmail.com>
wrote:
>I need to average the resulting averages from the query below
>This query gets the averages for each TowerNumber.
>SELECT LocationID, SystemID, TowerNumber, COUNT(TowerNumber) AS
>CountEntriesPerTowerNumber, AVG(SupplyCalciumHardness * 1.00)
> AS AvgSupplyCalciumHardness
>FROM tblTowers
>WHERE (ReadingDate BETWEEN @.BeginningDate AND @.EndingDate) AND (SystemID
>= @.SystemID) AND
> (LocationID = @.LocationID)
>GROUP BY LocationID, SystemID, TowerNumber
WITH ROLLUP
J.|||That comes up with 13.25, the correct answer is 11.75, so that doesn't work.
Any other idea?
"jxstern" <jxstern@.nowhere.xyz> wrote in message
news:kk8dl1p4cr94chpgo1vof4l8nefjusaekj@.4ax.com...
> On Wed, 19 Oct 2005 14:41:55 -0500, "Craig" <NoSpam@.hotmail.com>
> wrote:
>>I need to average the resulting averages from the query below
>>This query gets the averages for each TowerNumber.
>>SELECT LocationID, SystemID, TowerNumber, COUNT(TowerNumber) AS
>>CountEntriesPerTowerNumber, AVG(SupplyCalciumHardness * 1.00)
>> AS AvgSupplyCalciumHardness
>>FROM tblTowers
>>WHERE (ReadingDate BETWEEN @.BeginningDate AND @.EndingDate) AND
>>(SystemID
>>= @.SystemID) AND
>> (LocationID = @.LocationID)
>>GROUP BY LocationID, SystemID, TowerNumber
> WITH ROLLUP
>
> J.
>|||On Wed, 19 Oct 2005 15:14:44 -0500, "Craig" <NoSpam@.hotmail.com>
wrote:
>That comes up with 13.25, the correct answer is 11.75, so that doesn't work.
>Any other idea?
You can always store the first set of results to a temp table and then
run an average on them before returning them as a set.
SELECT
LocationID, SystemID, TowerNumber,
COUNT(TowerNumber) AS CountEntriesPerTowerNumber,
AVG(SupplyCalciumHardness * 1.00) AS AvgSupplyCalciumHardness
INTO #mytemp
FROM tblTowers
WHERE (ReadingDate BETWEEN @.BeginningDate AND @.EndingDate)
AND (SystemID = @.SystemID)
AND (LocationID = @.LocationID)
GROUP BY LocationID, SystemID, TowerNumber
SELECT avg(AvgSupplyCalciumHardness) as avgavg
from #mytemp
Or, if ALL you want is the average-average, or you don't mind
computing everything twice, something like:
SELECT avg(AvgSupplyCalciumHardness) as avgavg
from
(
SELECT
LocationID, SystemID, TowerNumber,
COUNT(TowerNumber) AS CountEntriesPerTowerNumber,
AVG(SupplyCalciumHardness * 1.00) AS AvgSupplyCalciumHardness
FROM tblTowers
WHERE (ReadingDate BETWEEN @.BeginningDate AND @.EndingDate)
AND (SystemID = @.SystemID)
AND (LocationID = @.LocationID)
GROUP BY LocationID, SystemID, TowerNumber
) x
But for all I know, that may come out 13.25, too. Got some rounding
issues there, may need to cast to int to make them consistent.
Since the intermediate values you list are all ints, should the "real"
answer be 11, or 11.75, or 12?
Inquiring minds ...
J.|||Hello,
You may need to use decimal as data type instead of int. I test the
following code and it works fine:
create table avgt
(colID decimal,
colNo decimal)
insert into avgt values (1, 1)
insert into avgt values (1, 2)
insert into avgt values (1, 3)
insert into avgt values (1, 1)
insert into avgt values (1, 1)
insert into avgt values (1, 1)
insert into avgt values (2, 1)
insert into avgt values (2, 2)
insert into avgt values (2, 3)
select avg(avg1) from
(
select colID, avg(colNo) as avg1 from avgt
group by colID) as x
I hope the information is helpful.
Sophie Guo
Microsoft Online Partner Support
Get Secure! - www.microsoft.com/security
=====================================================When responding to posts, please "Reply to Group" via your newsreader so
that others may learn and benefit from your issue.
=====================================================This posting is provided "AS IS" with no warranties, and confers no rights.

Averaging the Averages

I need to average the resulting averages from the query below
This query gets the averages for each TowerNumber.
SELECT LocationID, SystemID, TowerNumber, COUNT(TowerNumber) AS
CountEntriesPerTowerNumber, AVG(SupplyCalciumHardness * 1.00)
AS AvgSupplyCalciumHardness
FROM tblTowers
WHERE (ReadingDate BETWEEN @.BeginningDate AND @.EndingDate) AND (SystemID
= @.SystemID) AND
(LocationID = @.LocationID)
GROUP BY LocationID, SystemID, TowerNumber
What I really want is the average of the averages for
AVG(SupplyCalciumHardness * 1.00).
Something like this AVG(AVG(SupplyCalciumHardness * 1.00))
TowerNumber AvgSupplyCalciumHardness
1 14
2 18
3 7
4 8
Sum of averages = 47
Avg of Avg = 47 / 4 = 11.75
How can I do this?
Thanks
On Wed, 19 Oct 2005 14:41:55 -0500, "Craig" <NoSpam@.hotmail.com>
wrote:
>I need to average the resulting averages from the query below
>This query gets the averages for each TowerNumber.
>SELECT LocationID, SystemID, TowerNumber, COUNT(TowerNumber) AS
>CountEntriesPerTowerNumber, AVG(SupplyCalciumHardness * 1.00)
> AS AvgSupplyCalciumHardness
>FROM tblTowers
>WHERE (ReadingDate BETWEEN @.BeginningDate AND @.EndingDate) AND (SystemID
>= @.SystemID) AND
> (LocationID = @.LocationID)
>GROUP BY LocationID, SystemID, TowerNumber
WITH ROLLUP
J.
|||That comes up with 13.25, the correct answer is 11.75, so that doesn't work.
Any other idea?
"jxstern" <jxstern@.nowhere.xyz> wrote in message
news:kk8dl1p4cr94chpgo1vof4l8nefjusaekj@.4ax.com...
> On Wed, 19 Oct 2005 14:41:55 -0500, "Craig" <NoSpam@.hotmail.com>
> wrote:
> WITH ROLLUP
>
> J.
>
|||On Wed, 19 Oct 2005 15:14:44 -0500, "Craig" <NoSpam@.hotmail.com>
wrote:
>That comes up with 13.25, the correct answer is 11.75, so that doesn't work.
>Any other idea?
You can always store the first set of results to a temp table and then
run an average on them before returning them as a set.
SELECT
LocationID, SystemID, TowerNumber,
COUNT(TowerNumber) AS CountEntriesPerTowerNumber,
AVG(SupplyCalciumHardness * 1.00) AS AvgSupplyCalciumHardness
INTO #mytemp
FROM tblTowers
WHERE (ReadingDate BETWEEN @.BeginningDate AND @.EndingDate)
AND (SystemID = @.SystemID)
AND (LocationID = @.LocationID)
GROUP BY LocationID, SystemID, TowerNumber
SELECT avg(AvgSupplyCalciumHardness) as avgavg
from #mytemp
Or, if ALL you want is the average-average, or you don't mind
computing everything twice, something like:
SELECT avg(AvgSupplyCalciumHardness) as avgavg
from
(
SELECT
LocationID, SystemID, TowerNumber,
COUNT(TowerNumber) AS CountEntriesPerTowerNumber,
AVG(SupplyCalciumHardness * 1.00) AS AvgSupplyCalciumHardness
FROM tblTowers
WHERE (ReadingDate BETWEEN @.BeginningDate AND @.EndingDate)
AND (SystemID = @.SystemID)
AND (LocationID = @.LocationID)
GROUP BY LocationID, SystemID, TowerNumber
) x
But for all I know, that may come out 13.25, too. Got some rounding
issues there, may need to cast to int to make them consistent.
Since the intermediate values you list are all ints, should the "real"
answer be 11, or 11.75, or 12?
Inquiring minds ...
J.
|||Hello,
You may need to use decimal as data type instead of int. I test the
following code and it works fine:
create table avgt
(colID decimal,
colNo decimal)
insert into avgt values (1, 1)
insert into avgt values (1, 2)
insert into avgt values (1, 3)
insert into avgt values (1, 1)
insert into avgt values (1, 1)
insert into avgt values (1, 1)
insert into avgt values (2, 1)
insert into avgt values (2, 2)
insert into avgt values (2, 3)
select avg(avg1) from
(
select colID, avg(colNo) as avg1 from avgt
group by colID) as x
I hope the information is helpful.
Sophie Guo
Microsoft Online Partner Support
Get Secure! - www.microsoft.com/security
================================================== ===
When responding to posts, please "Reply to Group" via your newsreader so
that others may learn and benefit from your issue.
================================================== ===
This posting is provided "AS IS" with no warranties, and confers no rights.

Averaging the Averages

I need to average the resulting averages from the query below
This query gets the averages for each TowerNumber.
SELECT LocationID, SystemID, TowerNumber, COUNT(TowerNumber) AS
CountEntriesPerTowerNumber, AVG(SupplyCalciumHardness * 1.00)
AS AvgSupplyCalciumHardness
FROM tblTowers
WHERE (ReadingDate BETWEEN @.BeginningDate AND @.EndingDate) AND (SystemID
= @.SystemID) AND
(LocationID = @.LocationID)
GROUP BY LocationID, SystemID, TowerNumber
What I really want is the average of the averages for
AVG(SupplyCalciumHardness * 1.00).
Something like this AVG(AVG(SupplyCalciumHardness * 1.00))
TowerNumber AvgSupplyCalciumHardness
1 14
2 18
3 7
4 8
--
Sum of averages = 47
Avg of Avg = 47 / 4 = 11.75
How can I do this?
ThanksOn Wed, 19 Oct 2005 14:41:55 -0500, "Craig" <NoSpam@.hotmail.com>
wrote:
>I need to average the resulting averages from the query below
>This query gets the averages for each TowerNumber.
>SELECT LocationID, SystemID, TowerNumber, COUNT(TowerNumber) AS
>CountEntriesPerTowerNumber, AVG(SupplyCalciumHardness * 1.00)
> AS AvgSupplyCalciumHardness
>FROM tblTowers
>WHERE (ReadingDate BETWEEN @.BeginningDate AND @.EndingDate) AND (SystemI
D
>= @.SystemID) AND
> (LocationID = @.LocationID)
>GROUP BY LocationID, SystemID, TowerNumber
WITH ROLLUP
J.|||That comes up with 13.25, the correct answer is 11.75, so that doesn't work.
Any other idea?
"jxstern" <jxstern@.nowhere.xyz> wrote in message
news:kk8dl1p4cr94chpgo1vof4l8nefjusaekj@.
4ax.com...
> On Wed, 19 Oct 2005 14:41:55 -0500, "Craig" <NoSpam@.hotmail.com>
> wrote:
> WITH ROLLUP
>
> J.
>|||On Wed, 19 Oct 2005 15:14:44 -0500, "Craig" <NoSpam@.hotmail.com>
wrote:
>That comes up with 13.25, the correct answer is 11.75, so that doesn't work
.
>Any other idea?
You can always store the first set of results to a temp table and then
run an average on them before returning them as a set.
SELECT
LocationID, SystemID, TowerNumber,
COUNT(TowerNumber) AS CountEntriesPerTowerNumber,
AVG(SupplyCalciumHardness * 1.00) AS AvgSupplyCalciumHardness
INTO #mytemp
FROM tblTowers
WHERE (ReadingDate BETWEEN @.BeginningDate AND @.EndingDate)
AND (SystemID = @.SystemID)
AND (LocationID = @.LocationID)
GROUP BY LocationID, SystemID, TowerNumber
SELECT avg(AvgSupplyCalciumHardness) as avgavg
from #mytemp
Or, if ALL you want is the average-average, or you don't mind
computing everything twice, something like:
SELECT avg(AvgSupplyCalciumHardness) as avgavg
from
(
SELECT
LocationID, SystemID, TowerNumber,
COUNT(TowerNumber) AS CountEntriesPerTowerNumber,
AVG(SupplyCalciumHardness * 1.00) AS AvgSupplyCalciumHardness
FROM tblTowers
WHERE (ReadingDate BETWEEN @.BeginningDate AND @.EndingDate)
AND (SystemID = @.SystemID)
AND (LocationID = @.LocationID)
GROUP BY LocationID, SystemID, TowerNumber
) x
But for all I know, that may come out 13.25, too. Got some rounding
issues there, may need to cast to int to make them consistent.
Since the intermediate values you list are all ints, should the "real"
answer be 11, or 11.75, or 12?
Inquiring minds ...
J.|||Hello,
You may need to use decimal as data type instead of int. I test the
following code and it works fine:
create table avgt
(colID decimal,
colNo decimal)
insert into avgt values (1, 1)
insert into avgt values (1, 2)
insert into avgt values (1, 3)
insert into avgt values (1, 1)
insert into avgt values (1, 1)
insert into avgt values (1, 1)
insert into avgt values (2, 1)
insert into avgt values (2, 2)
insert into avgt values (2, 3)
select avg(avg1) from
(
select colID, avg(colNo) as avg1 from avgt
group by colID) as x
I hope the information is helpful.
Sophie Guo
Microsoft Online Partner Support
Get Secure! - www.microsoft.com/security
========================================
=============
When responding to posts, please "Reply to Group" via your newsreader so
that others may learn and benefit from your issue.
========================================
=============
This posting is provided "AS IS" with no warranties, and confers no rights.

Averaging on Multiple Levels.

Hello,

I am extremely new at MDX and Analysis Services.

I have read through chapter 7 of the fast track to MDX.It is a wonderful book.But it talks a lot about sales / time data.I know this is the way 99% of people will use cubes.I am trying to use a cube for another purpose and am having a hard time moving concepts on how we do things.I am the IT person for a Truss Plant in Northern Wisconsin.

This business is a job shop, 99% of the production is custom built trusses. Quoting the jobs for this type of business is always a struggle I would like to provide valuable information to management on how much it costs to build the trusses.I know this is an almost impossible task as the rules change constantly, but I believe we can use analysis services to help us find patterns in the data.I am hoping it has the capability to average measures on multiple levels without having to know what levels we want beforehand.

We have been collecting production times for about a year now. We don’t know exactly what we are looking for as of yet, but we have some ideas.I am thinking we need to start with pieces completed per man hour. But I would like to see how this changes based on all kinds of different dimensions (Truss Properties).

We have a measure called run_time_per_piece (Lumber pieces in the truss) . I would like to analyze what the average is based on different combinations of dimensions / hierarchies.I have tried using the standard AverageofChildren, but this is not working as I would think it should.I was expecting it to Average all returned values at each level no matter what the level is.One issue here is I don’t know what the hierarchies are going to be ahead of time. We only have a one measure we are looking at, at any one time but we don’t know what the hierarchies could be.

I would appreciate it if anyone could point me in the right direction. Am I using the right product for what I am trying to do?If so, how would I write the MDX statement to get the cube to average on all levels correctly.

Thanks in Advance.

Leo

Hi Leo,

If you browse the "Average Sales Amount" calculated measure in the Adventure Works cube, does it behave as you want, across hierarchies and levels? It is defined as:

Create Member CurrentCube.[Measures].[Average Sales Amount]

As [Measures].[Sales Amount]

/

[Measures].[Order Count],

Format_String = "Currency" ;

This is a common pattern for computing averages which work across all hierarchies, so it may help.

Averaging a set of columns in the same table

I have a table which has 100 columns with appx 20000 rows
Each column represents a set of data from a different source.
Anywhere from 1 to 100 of the columns may contain data (empty will be null)
I need to find a way to average accross the rows depending on the number of
rows which have data
Ie for first row
(Col1 + Col2 + Ccol3...Col34) / 34 where only col1 to col34 have data.
or
(Col1 + Col2 + Ccol3) / 3 where only col1 to col3 have data.
Note number of rows will always be the same for every column appx 20000
I know I could convert the horizontal data into vertical but this will mean
a table of over 2million rows. Other constarints force me to aviod this.
Any ideas ?
Hi
Normalize the table and then you can do averages over the groups (as you now
have rows) using the group by clause.
Your table design is not conducive to easy programming.
Regards
Mike Epprecht, Microsoft SQL Server MVP
Zurich, Switzerland
IM: mike@.epprecht.net
MVP Program: http://www.microsoft.com/mvp
Blog: http://www.msmvps.com/epprecht/
"Jinx1966" <Jinx1966@.discussions.microsoft.com> wrote in message
news:D9C74420-C0A1-400B-8518-3CE1BE0B19D5@.microsoft.com...
>I have a table which has 100 columns with appx 20000 rows
> Each column represents a set of data from a different source.
> Anywhere from 1 to 100 of the columns may contain data (empty will be
> null)
> I need to find a way to average accross the rows depending on the number
> of
> rows which have data
> Ie for first row
> (Col1 + Col2 + Ccol3...Col34) / 34 where only col1 to col34 have data.
> or
> (Col1 + Col2 + Ccol3) / 3 where only col1 to col3 have data.
>
> Note number of rows will always be the same for every column appx 20000
> I know I could convert the horizontal data into vertical but this will
> mean
> a table of over 2million rows. Other constarints force me to aviod this.
> Any ideas ?
|||> I know I could convert the horizontal data into vertical but this will mean
> a table of over 2million rows.
And... ?

> Other constarints force me to aviod this.
What constraints? This design is likely to be an awful nightmare and
performance bottleneck. The cost of supporting it is surely far greater
than that of fixing it.
but ...
(COALESCE(col1,0)+COALESCE(col2,0)+COALESCE(col3,0 ) ...)/
(CASE WHEN col1 IS NOT NULL THEN 1 END +
CASE WHEN col2 IS NOT NULL THEN 1 END +
CASE WHEN col3 IS NOT NULL THEN 1 END +
...)
(yuck!)
David Portas
SQL Server MVP
|||Thanks Mike.
"Mike Epprecht (SQL MVP)" wrote:

> Hi
> Normalize the table and then you can do averages over the groups (as you now
> have rows) using the group by clause.
> Your table design is not conducive to easy programming.
> Regards
> --
> Mike Epprecht, Microsoft SQL Server MVP
> Zurich, Switzerland
> IM: mike@.epprecht.net
> MVP Program: http://www.microsoft.com/mvp
> Blog: http://www.msmvps.com/epprecht/
> "Jinx1966" <Jinx1966@.discussions.microsoft.com> wrote in message
> news:D9C74420-C0A1-400B-8518-3CE1BE0B19D5@.microsoft.com...
>
>
|||Thanks David. I quite agree with your comments. Only you know how it can be,
small machines with small resources run by people with small brains and ears.
Now I have your and Mikes opinions I can get this issue fixed the proper
way...
Appreciate your time.
Regards
"David Portas" wrote:

> And... ?
>
> What constraints? This design is likely to be an awful nightmare and
> performance bottleneck. The cost of supporting it is surely far greater
> than that of fixing it.
> but ...
> (COALESCE(col1,0)+COALESCE(col2,0)+COALESCE(col3,0 ) ...)/
> (CASE WHEN col1 IS NOT NULL THEN 1 END +
> CASE WHEN col2 IS NOT NULL THEN 1 END +
> CASE WHEN col3 IS NOT NULL THEN 1 END +
> ...)
> (yuck!)
> --
> David Portas
> SQL Server MVP
> --
>

Averaging a set of columns in the same table

I have a table which has 100 columns with appx 20000 rows
Each column represents a set of data from a different source.
Anywhere from 1 to 100 of the columns may contain data (empty will be null)
I need to find a way to average accross the rows depending on the number of
rows which have data
Ie for first row
(Col1 + Col2 + Ccol3...Col34) / 34 where only col1 to col34 have data.
or
(Col1 + Col2 + Ccol3) / 3 where only col1 to col3 have data.
Note number of rows will always be the same for every column appx 20000
I know I could convert the horizontal data into vertical but this will mean
a table of over 2million rows. Other constarints force me to aviod this.
Any ideas ?Hi
Normalize the table and then you can do averages over the groups (as you now
have rows) using the group by clause.
Your table design is not conducive to easy programming.
Regards
--
Mike Epprecht, Microsoft SQL Server MVP
Zurich, Switzerland
IM: mike@.epprecht.net
MVP Program: http://www.microsoft.com/mvp
Blog: http://www.msmvps.com/epprecht/
"Jinx1966" <Jinx1966@.discussions.microsoft.com> wrote in message
news:D9C74420-C0A1-400B-8518-3CE1BE0B19D5@.microsoft.com...
>I have a table which has 100 columns with appx 20000 rows
> Each column represents a set of data from a different source.
> Anywhere from 1 to 100 of the columns may contain data (empty will be
> null)
> I need to find a way to average accross the rows depending on the number
> of
> rows which have data
> Ie for first row
> (Col1 + Col2 + Ccol3...Col34) / 34 where only col1 to col34 have data.
> or
> (Col1 + Col2 + Ccol3) / 3 where only col1 to col3 have data.
>
> Note number of rows will always be the same for every column appx 20000
> I know I could convert the horizontal data into vertical but this will
> mean
> a table of over 2million rows. Other constarints force me to aviod this.
> Any ideas ?|||> I know I could convert the horizontal data into vertical but this will mean">
> a table of over 2million rows.
And... ?

> Other constarints force me to aviod this.
What constraints? This design is likely to be an awful nightmare and
performance bottleneck. The cost of supporting it is surely far greater
than that of fixing it.
but ...
(COALESCE(col1,0)+COALESCE(col2,0)+COALE
SCE(col3,0) ...)/
(CASE WHEN col1 IS NOT NULL THEN 1 END +
CASE WHEN col2 IS NOT NULL THEN 1 END +
CASE WHEN col3 IS NOT NULL THEN 1 END +
..)
(yuck!)
David Portas
SQL Server MVP
--|||Thanks Mike.
"Mike Epprecht (SQL MVP)" wrote:

> Hi
> Normalize the table and then you can do averages over the groups (as you n
ow
> have rows) using the group by clause.
> Your table design is not conducive to easy programming.
> Regards
> --
> Mike Epprecht, Microsoft SQL Server MVP
> Zurich, Switzerland
> IM: mike@.epprecht.net
> MVP Program: http://www.microsoft.com/mvp
> Blog: http://www.msmvps.com/epprecht/
> "Jinx1966" <Jinx1966@.discussions.microsoft.com> wrote in message
> news:D9C74420-C0A1-400B-8518-3CE1BE0B19D5@.microsoft.com...
>
>|||Thanks David. I quite agree with your comments. Only you know how it can be
,
small machines with small resources run by people with small brains and ears
.
Now I have your and Mikes opinions I can get this issue fixed the proper
way...
Appreciate your time.
Regards
"David Portas" wrote:

> And... ?
>
> What constraints? This design is likely to be an awful nightmare and
> performance bottleneck. The cost of supporting it is surely far greater
> than that of fixing it.
> but ...
> (COALESCE(col1,0)+COALESCE(col2,0)+COALE
SCE(col3,0) ...)/
> (CASE WHEN col1 IS NOT NULL THEN 1 END +
> CASE WHEN col2 IS NOT NULL THEN 1 END +
> CASE WHEN col3 IS NOT NULL THEN 1 END +
> ...)
> (yuck!)
> --
> David Portas
> SQL Server MVP
> --
>

Averaging a set of columns in the same table

I have a table which has 100 columns with appx 20000 rows
Each column represents a set of data from a different source.
Anywhere from 1 to 100 of the columns may contain data (empty will be null)
I need to find a way to average accross the rows depending on the number of
rows which have data
Ie for first row
(Col1 + Col2 + Ccol3...Col34) / 34 where only col1 to col34 have data.
or
(Col1 + Col2 + Ccol3) / 3 where only col1 to col3 have data.
Note number of rows will always be the same for every column appx 20000
I know I could convert the horizontal data into vertical but this will mean
a table of over 2million rows. Other constarints force me to aviod this.
Any ideas ?Hi
Normalize the table and then you can do averages over the groups (as you now
have rows) using the group by clause.
Your table design is not conducive to easy programming.
Regards
--
Mike Epprecht, Microsoft SQL Server MVP
Zurich, Switzerland
IM: mike@.epprecht.net
MVP Program: http://www.microsoft.com/mvp
Blog: http://www.msmvps.com/epprecht/
"Jinx1966" <Jinx1966@.discussions.microsoft.com> wrote in message
news:D9C74420-C0A1-400B-8518-3CE1BE0B19D5@.microsoft.com...
>I have a table which has 100 columns with appx 20000 rows
> Each column represents a set of data from a different source.
> Anywhere from 1 to 100 of the columns may contain data (empty will be
> null)
> I need to find a way to average accross the rows depending on the number
> of
> rows which have data
> Ie for first row
> (Col1 + Col2 + Ccol3...Col34) / 34 where only col1 to col34 have data.
> or
> (Col1 + Col2 + Ccol3) / 3 where only col1 to col3 have data.
>
> Note number of rows will always be the same for every column appx 20000
> I know I could convert the horizontal data into vertical but this will
> mean
> a table of over 2million rows. Other constarints force me to aviod this.
> Any ideas ?|||> I know I could convert the horizontal data into vertical but this will mean
> a table of over 2million rows.
And... ?
> Other constarints force me to aviod this.
What constraints? This design is likely to be an awful nightmare and
performance bottleneck. The cost of supporting it is surely far greater
than that of fixing it.
but ...
(COALESCE(col1,0)+COALESCE(col2,0)+COALESCE(col3,0) ...)/
(CASE WHEN col1 IS NOT NULL THEN 1 END +
CASE WHEN col2 IS NOT NULL THEN 1 END +
CASE WHEN col3 IS NOT NULL THEN 1 END +
...)
(yuck!)
--
David Portas
SQL Server MVP
--|||Thanks Mike.
"Mike Epprecht (SQL MVP)" wrote:
> Hi
> Normalize the table and then you can do averages over the groups (as you now
> have rows) using the group by clause.
> Your table design is not conducive to easy programming.
> Regards
> --
> Mike Epprecht, Microsoft SQL Server MVP
> Zurich, Switzerland
> IM: mike@.epprecht.net
> MVP Program: http://www.microsoft.com/mvp
> Blog: http://www.msmvps.com/epprecht/
> "Jinx1966" <Jinx1966@.discussions.microsoft.com> wrote in message
> news:D9C74420-C0A1-400B-8518-3CE1BE0B19D5@.microsoft.com...
> >I have a table which has 100 columns with appx 20000 rows
> > Each column represents a set of data from a different source.
> > Anywhere from 1 to 100 of the columns may contain data (empty will be
> > null)
> >
> > I need to find a way to average accross the rows depending on the number
> > of
> > rows which have data
> >
> > Ie for first row
> > (Col1 + Col2 + Ccol3...Col34) / 34 where only col1 to col34 have data.
> > or
> > (Col1 + Col2 + Ccol3) / 3 where only col1 to col3 have data.
> >
> >
> > Note number of rows will always be the same for every column appx 20000
> >
> > I know I could convert the horizontal data into vertical but this will
> > mean
> > a table of over 2million rows. Other constarints force me to aviod this.
> >
> > Any ideas ?
>
>|||Thanks David. I quite agree with your comments. Only you know how it can be,
small machines with small resources run by people with small brains and ears.
Now I have your and Mikes opinions I can get this issue fixed the proper
way...
Appreciate your time.
Regards
"David Portas" wrote:
> > I know I could convert the horizontal data into vertical but this will mean
> > a table of over 2million rows.
> And... ?
> > Other constarints force me to aviod this.
> What constraints? This design is likely to be an awful nightmare and
> performance bottleneck. The cost of supporting it is surely far greater
> than that of fixing it.
> but ...
> (COALESCE(col1,0)+COALESCE(col2,0)+COALESCE(col3,0) ...)/
> (CASE WHEN col1 IS NOT NULL THEN 1 END +
> CASE WHEN col2 IS NOT NULL THEN 1 END +
> CASE WHEN col3 IS NOT NULL THEN 1 END +
> ...)
> (yuck!)
> --
> David Portas
> SQL Server MVP
> --
>