Help with avg Query

  • Thread starter Thread starter Pejo
  • Start date Start date
P

Pejo

I want to create an average but throw away the top and bottom numbers from
the average.

Something like the below (which doesn't work)

Select avg (select hour_count from tbl_test where ID not in (select top 1 ID
from tbl_test order by hour_count desc) and ID not in (select top 1 from
tbl_test order by hour_count asc)
 
Hi,


untested, but something like this should do:

SELECT avg(toto)
FROM somewhere
WHERE NOT pk IN(
SELECT TOP 1 toto
FROM somewhere
WHERE NOT toto IS NULL
ORDER BY toto ASC, pk

UNION ALL

SELECT TOP 1
FROM somewhere
WHERE Not toto IS NULL
ORDER BY toto DESC, pk)


where I assumed pk is the primary key field (that would indeed always remove
just one record, even in case there are many record with the same top 1
toto-value).



Hoping it may help,
Vanderghast, Access MVP
 
Back
Top