CODEWITHSUNDEEP
sqlsql-server
JC Borlagdan
JC Borlagdan

Reputation: 3618

SQL Average Incorrect

I have the following query which returns

Rate
------
4
6
8


SELECT Rate FROM t_Vote 
EXCEPT 
SELECT MAX(Rate) FROM t_Vote 
EXCEPT 
SELECT MIN(Rate) FROM t_Vote

My aim is to get the average of those 3 numbers so I just need to replace the first SELECT Rate with SELECT AVG(Rate). But instead of getting 6 the value that I get with SELECT AVG(Rate) is 5. Why is this like this? And When I try to SELECT AVG(CAST(Rate AS FLOAT)), it doesn't consider the EXCEPT condition. What am I missing?

Upvotes: 0

Views: 269

Answers (3)

jarlh
jarlh

Reputation: 44746

EXCEPT solution will remove duplicates before calculating the average!

Simply remove max and min manually, and then calculate the average:

select (sum(Rate) - max(Rate) - min(Rate)) / (count(Rate) - 2)
from t_Vote

Warning: Will only work if there are at least 3 rows in the table!

Perhaps you'll need to multiply the sum with 1.0 to get float result? (Required by some dbms products, but not by others.)

Upvotes: 2

Bharat Prasad Satyal
Bharat Prasad Satyal

Reputation: 79

If you need just the average. You just need to run below query. 

SELECT AVG(Rate) from t_vote

There is no need to write other two lines that have EXCEPT on it. I tested the above scenario and it is working properly in my machine(SQL 2008). Now the problem could be there are also other records in table which you don't want to include in average calculation such as 0, max value or min value. If so make sure you exclude those values as below. In below example I just excluded max value and min value. 

Select AVG(Rate) from (
 SELECT Rate from t_vote
 EXCEPT 
 SELECT MAX(Rate) FROM t_Vote 
 EXCEPT 
 SELECT MIN(Rate) FROM t_Vote
)a

Upvotes: 0

TheGameiswar
TheGameiswar

Reputation: 28890

My aim is to get the average of those 3 numbers so I just need to replace the first SELECT Rate with SELECT AVG(Rate)

You should do like below

:with cte(n)
as
(
SELECT Rate FROM t_Vote 
EXCEPT 
SELECT MAX(Rate) FROM t_Vote 
EXCEPT 
SELECT MIN(Rate) FROM t_Vote
)
select avg(n) from cte

Upvotes: 4

Related Questions