views:

133

answers:

3

How can you write the following in MYSQL?

SELECT AVG(col1) FROM table WHERE DISTINCT col2

more info:

table

col1 | col2
-----------
2    | 555.555.555.555
5    | 555.555.555.555
4    | 444.444.444.444

returns '3'

Basically I'm trying to select average value of col1 where ip addresses in col2 are distinct.

+2  A: 
  SELECT col2, 
         AVG(col1) 
    FROM table 
GROUP BY col2
Adam Bernier
So simple! Thanks!!
tim
Is there an easy way to get AVG() of the entire column?
tim
`select avg(col1) from table` would give you the average of the entire column.
Adam Bernier
A: 

Right, because the distinct clause would find the first and third rows, the average of 2 and 4 is 3.

What I think you're looking for is "group by col2" instead of distinct.

Mike Sherov
A: 

I think you want the group by operator. It will group the rows before running calculations on them.

dpb