use of geomean in query

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

i am not sure if this is the proper Group but i will post it here,
i want to calculate from a table the geomean of some values,
the table has a primary key id, suplier name, date, valuetm,
so for a period lets say 2 or 3 months i want to have the geomean result of the valuetm, is that possible? from a a query or with the help of a report;
any help would be great!
thank you for your time
 
Hi,


If you mean the geometric mean, you can compute it like this:


SELECT id, EXP(AVG(LOG( value))) As geoMean
FROM myTable
GROUP BY id


Sure, that assumes the value is not 0 neither negative, anywhere. Can add a
WHERE clause about it, but so, the data is basically wrong too (geometric
mean meaning nothing if data can be zero or negative).



Hoping it may help,

sotiris_s said:
i am not sure if this is the proper Group but i will post it here,
i want to calculate from a table the geomean of some values,
the table has a primary key id, suplier name, date, valuetm,
so for a period lets say 2 or 3 months i want to have the geomean result
Click to expand...
of the valuetm, is that possible? from a a query or with the help of a
report;
 
thank so much

Michel Walsh said:
Hi,


If you mean the geometric mean, you can compute it like this:


SELECT id, EXP(AVG(LOG( value))) As geoMean
FROM myTable
GROUP BY id


Sure, that assumes the value is not 0 neither negative, anywhere. Can add a
WHERE clause about it, but so, the data is basically wrong too (geometric
mean meaning nothing if data can be zero or negative).



Hoping it may help,


of the valuetm, is that possible? from a a query or with the help of a
report;
Click to expand...
 
I tried Michel's solution but was not successful. However, the following did
work for me.

A few preliminary things to understand.
1) All Geometric Mean (GM) data must have a value and can't have any null
values so I made a query from the table of data. In the query I ran a
criteria for "CFUWet" as "Is Not Null".
2) I wanted a different GM for each Site so it was grouped.
3) The expression below was named WetGM. I ran it and compared it with
excel. It worked perfectly.

Viewing in SQL
SELECT WetData.Site, Exp(Sum(Log([CFUWet])))^(1/Count([CFUWet])) AS WetGM
FROM WetData
GROUP BY WetData.Site;
 
Back
Top