Average Function Giving Wrong Average

Dotaz

• I've a larger query in which is a section where I try to create a table variable with pertinent data including the average of one of the fields in the source table.

When I run the code, it looked like it worked, giving me a number for the field, Average, that was the same for all rows. So, I thought it had worked. But, to check, I selected the column of data being averaged, pasted it to Excel and got a different number for the average.

Here's the code;

```DECLARE @TempTable_Metric_1 TABLE
(
ProvID int,
Metric_Month int,
Data_Desc varchar(max),
Metric float,
Average numeric(5,4)
)
INSERT INTO @TempTable_Metric_1
(
ProvID,
Metric_Month,
Data_Desc,
Metric,
Average
)
SELECT
Provider_ID AS ProvID,
Month_ID AS Metric_Month,
'Recommend Provider' AS Data_Desc,
Measure1 AS Metric,
(
SELECT
AVG(Measure1)
FROM BQA_Patient_Satisfaction
) AS Average
FROM Table1
WHERE Month_ID = @Month_ID```

Before I put in the averaging part - and the requisite fields - the query brought back the data it was supposed to just fine. Actually, it may still be doing so, because maybe there's a precision issue between Excel and SQL Server that futzes up the math; that, I don't know.

Does anything just pop out at anyone?

Oh, something I just thought of - that sorta reinforces my precision issue thought - is that this is one of three table variables each which deals with a different metric. One of the tables, however, is a metric for which we have no data yet, for future expansion. In that table, I'm forcing a 0 for the metric. Now, when that table is examined, it does show a 0 for the average.

• Upravený 20. srpna 2012 20:53 Sudden thought
20. srpna 2012 20:50

Odpovědi

• What is the Measure1 column's type and are you selecting this column from the same table as the main select? If so, you can also use window aggregate function, e.g.

AVERAGE (Measure1) OVER () to get average value for the table.

Also, in your case you're getting the average for the whole table, not for the particular month you're running the query for.

For every expert, there is an equal and opposite expert. - Becker's Law

My blog

20. srpna 2012 20:54
• Because you are using a subquery :

SELECT AVG(Measure1)
FROM BQA_Patient_Satisfaction

you are averaging every value of Measure1 in BQA_Patient_Satisfaction. Is that really what you meant?

I think you want to qualify this by month.

SELECT AVG(Measure1)
FROM BQA_Patient_Satisfaction
WHERE Month_ID = @Month_ID

• Označen jako odpověď 21. srpna 2012 15:12
20. srpna 2012 21:00

Všechny reakce

• What is the Measure1 column's type and are you selecting this column from the same table as the main select? If so, you can also use window aggregate function, e.g.

AVERAGE (Measure1) OVER () to get average value for the table.

Also, in your case you're getting the average for the whole table, not for the particular month you're running the query for.

For every expert, there is an equal and opposite expert. - Becker's Law

My blog

20. srpna 2012 20:54
• Because you are using a subquery :

SELECT AVG(Measure1)
FROM BQA_Patient_Satisfaction

you are averaging every value of Measure1 in BQA_Patient_Satisfaction. Is that really what you meant?

I think you want to qualify this by month.

SELECT AVG(Measure1)
FROM BQA_Patient_Satisfaction
WHERE Month_ID = @Month_ID

• Označen jako odpověď 21. srpna 2012 15:12
20. srpna 2012 21:00
• Because you are using a subquery :

SELECT AVG(Measure1)
FROM BQA_Patient_Satisfaction

you are averaging every value of Measure1 in BQA_Patient_Satisfaction. Is that really what you meant?

I think you want to qualify this by month.

SELECT AVG(Measure1)
FROM BQA_Patient_Satisfaction
WHERE Month_ID = @Month_ID

Uh, yeah - well, of course.

Excuse me while I go stand in the corner and examine the quality of the paint.

21. srpna 2012 15:12