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

Candelaria

Can you share the output you're expecting to get for this data? Your calculation and your text seem to contradict each other. – Libidinous

Answer 1

Try this:

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

Streeter

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

Skuld

Answer 3

Average is the sum of a set of values divided by the number of those value. Here, you want to divide by a count of the distinct values, so:

SELECT SUM(col2)/COUNT (DISTINCT col1)
FROM   my_table

Libidinous

Dear, you summarize all valus of col2, not one for distinct col1. – Streeter

This is what the OP's text suggests, albeit not his example. Let's ask the OP for clarifications – Libidinous

Answer 4

You can just say

SELECT col1, AVG( col2 ) FROM my_table GROUP BY col1

Leiker

This is wrong, it produces avg for each col1 distinct element separately! – Candelaria

Answer 5

Try

SELECT AVG(coll2) as Average From myTable Group by Col1,Col2

Notify