Difference between value and avg(value)

Question

I wonder if it is possible to make query like following one.

I have a table that contains id and value. Exapmle:

 table EXAMPLE
|id        |  value     |
|        1 |          65|
|        2 |          13|
|        3 |          22|

What I want is to make query that returns the set like this:

|id        |  value     | average | difference|   
|        2 |          13|     33.3|       20.3|
|        3 |          22|     33.3|       11.3|

The question is how to deal with different column ?

And one more question: How to include in the set only values, that are less or great then average ,

SELECT id,
       value,
       (SELECT AVG(value) FROM EXAMPLE ) as average
having 
       value <  average.

The reason i am using having instead of where is that where is executing before select and therefore i cant use average in it (average is computed in the select phase of query execution).

I also suspect that I am calculating AVG(value) not only once, but for every row in the table. Am i right ? If i am, that is pretty bad and it is surely not what i wanted.

Please get some light for me on the MySQL execution sequence of subqueries or share some links for this theme. Thank you in advance.

Answer 1

You'll have to calculate the overall average separately and force a cross join:

SQL Fiddle

select
    id,
    value,
    avgValue,
    avgValue - value as Diff
from
    table1
  cross join
    ( 
      select
          avg(value) as AvgValue
      from
          table1
    ) t2
where
    value < avgValue