Logo Questions Linux Laravel Mysql Ubuntu Git Menu
 

Find the top 5 MAX() values from an SQL table and then performi an AVG() on that table without them

Tags:

sql

oracle

I want to be able to perform an avg() on a column after removing the 5 highest values in it and see that the stddev is not above a certain number. This has to be done entirely as a PL/SQL query.

EDIT: To clarify, I have a data set that contains values in a certain range and tracks latency. I want to know whether the AVG() of those values is due to a general rise in latency, or due to a few values with a very high stddev. I.e - (1, 2, 1, 3, 12311) as opposed to (122, 124, 111, 212). I also need to achieve this via an SQL query due to our monitoring software's limitations.

like image 744
Arkadi Y. Avatar asked Jun 26 '11 11:06

Arkadi Y.


People also ask

How would you use the AVG () function in SQL?

SQL AVG function is used to find out the average of a field in various records. You can take average of various records set using GROUP BY clause. Following example will take average all the records related to a single person and you will have average typed pages by every person.

How do you SELECT the top 3 maximum value in SQL?

To get the maximum value from three different columns, use the GREATEST() function. Insert some records in the table using insert command. Display all records from the table using select statement.

How can I get max value in SQL without using max function?

You can do that as: select MIN(-1 * col)*-1 as col from tableName; Alternatively you can use the LIMIT clause if your database supports it.

Can we use AVG with WHERE in SQL?

The AVG() function is one of the aggregate functions that can be used in conjunction with the WHERE clause to gain more insights from our data. In SQL, the AVG() function is used to compute the average of numeric values in a column.


2 Answers

You can use row_number to find the top 5 values, and filter them out in a where clause:

select  avg(col1)
from    (
        select  row_number() over (order by col1 desc) as rn
        ,       *
        from    YourTable
        ) as SubQueryAlias
where   rn > 5
like image 101
Andomar Avatar answered Oct 27 '22 06:10

Andomar


select column_name1 from 
(
  select column_name1 from table_name order by nvl(column_name,0) desc
)a 
where rownum<6 

(the nvl is done to omit the null value if there is/are any in the column column_name)

like image 39
Souvik Saha Avatar answered Oct 27 '22 04:10

Souvik Saha