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

Can you share the output you're expecting to get for this data? Your calculation and your text seem to contradict each other. — Mureinik, Oct 15 '14 at 07:59

score 8 · Accepted Answer · answered Oct 15 '14 at 07:59

8

Try this:

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

answered Oct 15 '14 at 07:59

Joe Taras

score 5 · Answer 2 · answered Oct 15 '14 at 07:59

5

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

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

answered Oct 15 '14 at 07:59

Gordon Linoff

score 1 · Answer 3 · answered Oct 15 '14 at 07:54

1

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

answered Oct 15 '14 at 07:54

Mureinik

Dear, you summarize all valus of col2, not one for distinct col1. – Joe Taras Oct 15 '14 at 07:57
This is what the OP's **text** suggests, albeit not his example. Let's ask the OP for clarifications – Mureinik Oct 15 '14 at 07:58

score 0 · Answer 4 · answered Oct 15 '14 at 07:57

0

You can just say

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

answered Oct 15 '14 at 07:57

fallenland

3

This is wrong, it produces avg for each col1 distinct element separately! – BlueGirl Oct 15 '14 at 09:45

score -1 · Answer 5 · answered Oct 15 '14 at 07:58

-1

Try

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

answered Oct 15 '14 at 07:58

Nuru Salihu

5 Answers5