sql select average from distinct column of table

Question

In my table I have some colms like this, (beside another cols)

col1 | col2
s1   |  5
s1   |  5
s2   |  3
s2   |  3
s2   |  3
s3   |  5
s3   |  5
s4   |  7

I want to have average of ALL col2 over Distinct col1. (5+3+5+7)/4=5

Answer 1

Try this:

SELECT AVG(T.col2)
FROM 
    (SELECT DISTINCT col1, col2
    FROM yourtable) as T

Answer 2

You are going to need a subquery. Here is one way:

select avg(col2)
from (select distinct col1, col2
      from my_table
     ) t