CODEWITHSUNDEEP
sqlsql-server-2008database
Ajay Sainy
Ajay Sainy

Reputation: 379

Exclude row while calculation average using AVG in sql server

I have this table (Worker)

ID      NAME                 ST     SALARY       DEP_ID
-----       -------------------- --  ---------  ---------
1       Arun                 MH      55000          22
2       Manish               MP      53000          11
3       Rahul                GJ      45000          22

i want to get average salary and in that average I dont want to include minimum salary.

I wrote this query

select AVG(w.salary)
from Worker w 
where w.salary  >(select MIN(w.salary) from Worker w)

can this be done without using subquery?

Upvotes: 0

Views: 1380

Answers (3)

mbroshi
mbroshi

Reputation: 979

Well, average is just sum/count, so you can do:

select (SUM(w.salary) - MIN(w.salary)) / (COUNT(w.salary) - 1)
from Worker w

Edit: As per the comment, the above solution does not work if there is more than one minimum and you want to exclude all minima. To fix that without using a subquery, I can only think of using a cursor (which seems like overkill). Nonetheless, the following fits the requirement:

DECLARE @min int = NULL
DECLARE @numMin int = 1
DECLARE @sum int = 0
DECLARE @current int
DECLARE @count int = 0

DECLARE db_cursor CURSOR FOR  
SELECT salary
FROM Worker

OPEN db_cursor   
FETCH NEXT FROM db_cursor INTO @current

WHILE @@FETCH_STATUS = 0   
BEGIN
    IF @min IS NULL BEGIN
        SET @min = @current
    END
    ELSE IF @current < @min BEGIN
        SET @min = @current
        SET @numMin= 1
    END
    ELSE IF @current = @min BEGIN
        SET @numMin= @numMin + 1
    END

    SET @sum = @sum + @current  
    SET @count = @count + 1

    FETCH NEXT FROM db_cursor INTO @current   
END   

CLOSE db_cursor   
DEALLOCATE db_cursor

SELECT (@sum - @numMin * @min) / (@count - @numMin)

Upvotes: 2

Gordon Linoff
Gordon Linoff

Reputation: 1269703

I don't think you can really do this accurately without a subquery. The issue is that multiple records could have the minimum salary. In fact, all of the salaries could be the minimum and the average may not even be defined.

You can do:

select AVG(w.salary)
from (select w.*, min(w.salary) over () as minsalary
      from Worker w 
     ) w
where w.salary > minsalary;

That still has a subquery, but not in the where clause.

Upvotes: 1

AFetter
AFetter

Reputation: 3584

I don't know if this is the best pratics in SQL

Select (SUM(w.salary)-MIN(w.salary))/(COUNT(w.salary)-1) from Worker w

Upvotes: 2

Related Questions