stupid question Average aggregate function

  • Thread starter Thread starter Michael Beatty
  • Start date Start date
M

Michael Beatty

how do I create a column in a query that displays the average of all of the
values in the row?
 
Try

SELECT (value1 + value2 + .... + valueN)/N FROM ......

where value1 etc are the columns in the table with numeric
or currency datatype.

Hope This Helps
Gerald Stanley MCSD
 
Try replacing the divisor with

(IIf(value1 = 0, 0,1) + IIf(value2 = 0, 0,1)... +
IIf(valueN =0,0,1))

Hope This Helps
Gerald Stanley MCSD
 
To cope with nulls, try a divisor of
(IIf(Nz(value1,0) = 0, 0,1) + IIf(Nz(value2,0) = 0, 0,1)...
+ IIf(Nz(valueN,0) =0,0,1))

Hope This Helps
Gerald Stanley MCSD
 
To do this in a row, you need to do one of two things. The best is to normalize
your data, but you can use a function to do this.

Paste the function below into a module and then call it from your query.

Public Function fGetMeanAverage(ParamArray Values())
'John Spencer UMBC CHPDM
'Last Update: April 5, 2000
'Calculates the arithmetic average (mean) of a group of values passed to it.
'Sample call: myAvg = GetMeanAverage("1","TEST","2", "3",4,5,6,0) returns 3 (21/7)
'Ignores values that cannot be treated as numbers.

Dim i As Integer, intElementCount As Integer, dblSum As Double
intElementCount = 0
dblSum = 0

For i = LBound(Values) To UBound(Values)
If IsNumeric(Values(i)) Then 'Ignore Non-numeric values
dblSum = dblSum + Values(i)
intElementCount = intElementCount + 1
End If
Next i

If intElementCount > 0 Then
fGetMeanAverage = dblSum / intElementCount 'At least one number in the
group of values
Else
fGetMeanAverage = Null 'No number in the group of values
End If

End Function

Another method would be to make a union all query and then use that as the
source of a totals query.

SELECT PrimaryKeyField, ValueFieldA
FROM YourTable
UNION ALL
SELECT PrimaryKeyField, ValueFieldB
FROM YourTable
UNION ALL
SELECT PrimaryKeyField, ValueFieldC
FROM YourTable
....

Then

SELECT PrimaryKeyField, Avg(ValueFieldA)
FROM SavedUnionQuery
GROUP BY PrimaryKeyField
 
Back
Top