row level aggregate functions

[Thomas Spellman]

I need to use some functions like avg() and stdev(), but only on multiple
fields in the current row, not on a whole column, as is the default. Would
someone please recommend some way of doing this? For example, I have 3
columns, param1, param2, param3, and I want to show those on the report, but
to add three more calculated fields, paramMean, paramStdev, and
paramPrecision, based on those fields. I'm unable to use
avg(param1,param2,param3) as I am in excel, since it's an SQL aggregate
function. when I try to do it simply like "=(param1 + param2 + param3)/3"
then it fails to work if any of the fields are null, and would not be
accurate even if it considered a null as a zero. Any suggestions? I've
thought of writing a function, but it would need to be able to handle a
variable number of arguments. Is this possible?

thanks

 

[Rick Brandt]

I need to use some functions like avg() and stdev(), but only on
multiple fields in the current row, not on a whole column, as is the
default. Would someone please recommend some way of doing this? For
example, I have 3 columns, param1, param2, param3, and I want to show
those on the report, but to add three more calculated fields,
paramMean, paramStdev, and paramPrecision, based on those fields.
I'm unable to use avg(param1,param2,param3) as I am in excel, since
it's an SQL aggregate function. when I try to do it simply like
"=(param1 + param2 + param3)/3" then it fails to work if any of the
fields are null, and would not be accurate even if it considered a
null as a zero. Any suggestions? I've thought of writing a
function, but it would need to be able to handle a variable number of
arguments. Is this possible?

thanks

Not tested, but...

=(Nz(param1,0) + Nz(param2,0) + Nz(param3,0))/(IIf(IsNull(param1,0,1) +
IIf(IsNull(param2,0,1) + IIf(IsNull(param3,0,1))

 

[Thomas Spellman]

Looks good. I wound up writing a mean() function that takes an array, and
calling it with an array of each set of parameters within Detail_Format().
What about stdev? Would you do the same kind of thing? I presume this is
not better supported because it's not often done?

Thanks

T

 

[Rick Brandt]

Looks good. I wound up writing a mean() function that takes an array, and
calling it with an array of each set of parameters within Detail_Format().
What about stdev? Would you do the same kind of thing? I presume this is not
better supported because it's not often done?

More often than not having a need to aggregate across columns means that the
table design is incorrect.

 

[Duane Hookom]

I would recommend normalizing the table with a union query
SELECT FieldA, param1 As ParamValue, 1 as ParamNum
FROM tblA
UNION ALL
SELECT FieldA, param2, 2
FROM tblA
UNION ALL
SELECT FieldA, param3, 3
FROM tblA;

You can then use standard SQL aggregate functionality.

 

[Thomas Spellman]

I understand what you mean, in the abstract, but not specifically. Where
would you put this code? In the detail_format() function? Where would you
put the avg() and stdev() functions? I'm not seeing it.

Thanks for the tip though.

I'm now facing the problem of doing another report that graphs these means
and stdevs. So, one graph would display the calculated mean of one param (x
axis) from each row against the date field (y axis) of that row. There will
be 5 graphs on each page, one for each parameter, and about 15 pages, one
for each site. The chart wizard doesn't seem to want to do this.

T

 

[Douglas J. Steele]

Duane's suggesting you create a query that normalizes your data, and use
that query as the basis for your computations, rather than whatever you're
currently using.

What he gave you was the SQL associated with the query.

 

[Thomas Spellman]

I tried to add your suggested code as a subquery of my query that returns
all the fields of the table, but was told that it is not allowed in a
subquery. Please show me how to add new columns called pH_avg and pH_stdev
to the following query. Thank you.

SELECT TACdata.ID, TACdata.Date, TACdata.Site, TACdata.pH_1, TACdata.pH_2,
TACdata.pH_3
FROM TACdata;

 

[Duane Hookom]

I didn't provide any "suggested code". I did provide SQL that assumed field
names of param1, param2,.. etc.

I also wouldn't add new columns of avg and stdev. These would be aggregated
rows.

I believe your union query would be something like:
SELECT ID, ph_1 as PH, 1 as Reading
FROM TACData
UNION ALL
SELECT ID, ph_2, 2
FROM TACData
UNION ALL
SELECT ID, ph_3, 3
FROM TACData;

Save this as quniTACData. Then create a query based on the union query that
groups by ID and creates an avg or stdev on the PH column.