CODEWITHSUNDEEP
mysqlsql
xolodec
xolodec

Reputation: 843

Difference between value and avg(value)

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.

Upvotes: 3

Views: 2977

Answers (4)

oerkelens
oerkelens

Reputation: 5161

This also does the trick

select id, value,
       (select avg(Value) from table1) as avg,
       (select avg(Value) from table1) - value as Diff
from table1 having Diff > 0

Upvotes: 0

Andrew
Andrew

Reputation: 8703

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

Upvotes: 8

Konstantin
Konstantin

Reputation: 3294

select id, value, (select avg(value) from EXAMPLE ) as average, 
   (select avg(value) from EXAMPLE ) - value as difference
   from EXAMPLE ;

Upvotes: 0

Alden W.
Alden W.

Reputation: 1439

I think this would do it. You need to group in order to do you aggregate functions.

SELECT id, value, (SELECT AVG(value) FROM example GROUP BY id) AS average,
       average - value AS diff
FROM example
GROUP BY id
HAVING value < average;

Upvotes: 0

Related Questions