Re: Querying mean values without using "Totals"

Tech-Archive recommends: Repair Windows Errors & Optimize Windows Performance

You cannot use an aggregate in a WHERE clause. Try using it in an HAVING
clause. The WHERE clause is executed BEFORE any aggregation, so, the result
of the aggregation cannot be tested in it, at that stage.

SELECT id, AVG(something)
FROM somewhere
GROUP BY id
HAVING AVG(something) >= (SELECT AVG(something) FROM somewhere)


will return the groups 'id' where their average is larger than the average
of the whole table.



Vanderghast, Access MVP


"gabriel" <gabriel@xxxxxxxxxxxxxxxxxxxxxxxxx> wrote in message
news:7D879DE0-6781-4A36-BE8E-E9F98FBA779A@xxxxxxxxxxxxxxxx
Dear Access Community

I'm a novice regarding Access and have an apperently simple problem.
I want to query the average of a numeric field, without using the "Totals"
approach.
Using the expression builder with the function "Avg" in the cell
"criteria"
causes following error message (I translated the message from portuguese
into
english, so the original message is probably a little different: "It is
not
possible to have a aggregated function in a WHERE instance".

Can anybody help me ?

Thanks a lot !

gabriel


.

Relevant Pages

  • Re: What does "expression" refer to in the Totals list in a Query?
    ... Expressions are fields in the SELECT clause ... Sum means add up all values for the grouped by fields. ... Since the expression is tacked on after aggregation, ... You can therefore refer to that in your Expression. ...
    (microsoft.public.access.queries)
  • Re: Box query
    ... Changing the having clause won't fix a problem that already happened ... I'm changing the aggregation expression. ... intersect is a simple expression ...
    (comp.databases.theory)
  • Re: DateDiff
    ... The purpose of the GROUP BY clause is to perform some sort of aggregation on some field. ... When I take out the group by Employee id, the query runs fine. ...
    (microsoft.public.access.queries)
  • Re: Where Between Date Range
    ... AS 2005 makes possible using sets in the WHERE clause, ... 2005 engine makes aggregation on the fly. ... Vladimir Chtepa. ... > Deepak Puri ...
    (microsoft.public.sqlserver.olap)