user2273058 user2273058 - 7 months ago 8
SQL Question

Is it possible to compare each record in a table with the average values of other records in a query

For a table in MySQL I have two columns

col1 col2
1 4.5
1 4.8
1 4.4
1 3.9
1 7.6
1 4.5
2 2.9
2 4.8
2 5.6
2 4.5
3 8.9
3 9.7
3 7.8
3 8.2
3 4.3


what I want to do is


  1. calculate the average values of col2 for different values of col1 and this is easy



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

1 4.95
2 4.45
3 7.78


2. select the record if its col2 value is less than average of col2 values where col1 is equal to col1-1. I couldn't figure out this part:

SELECT col1,col2 FROM mytable WHERE col2<AVG(col2 where col1= current col1-1)


The result set should be:

2 2.9
2 4.8
2 4.5
3 4.3


Since there is no records where col1=0, there will be no record for col1=1

Answer

Having clause and self-join allows you to do this

Select a.col1, a.col2, 
   Avg(b.Col2) AvgCol2
From yourTable a
   Join yourTable b 
       On b.col1 = a.col1 - 1     
Group By a.col1, a.col2
Having a.col2 < Avg(b.Col2)

For your sample data, for e.g., it returns

col1     col2     AvgCol2
-------- -------- --------
2        2.9      4.95
3        4.3      4.45
2        4.5      4.95
2        4.8      4.95

Explanation: One way to think about this is that Having is clause for filtering the result set constructed AFTER Aggregation in a Group By query, whereas Where clause is filter for temporary resultset constructed just BEFORE Aggregation, (or before Sorting in a non Group By Query).

Comments