CODEWITHSUNDEEP
mysqlsql
user2273058
user2273058

Reputation: 33

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

Upvotes: 3

Views: 2300

Answers (3)

Richard Dev
Richard Dev

Reputation: 1170

The question matched what I was looking for but not the approach. I thought I would share for others looking for a different solution.

Try the OVER Partition Method.

avg(OrderValue) OVER(PARTITION BY industry)

https://learnsql.com/blog/over-clause-mysql/

Upvotes: 0

Mahmoud Gamal
Mahmoud Gamal

Reputation: 79959

Try this:

SELECT t1.*
FROM @yourtable AS t1
INNER JOIN
(
   SELECT col1, AVG(col2) col2avg
   FROM @yourtable
   GROUP BY col1
) AS t2  ON t1.col1 - 1 = t2.col1
        AND t1.col2 < t2.col2avg;

Upvotes: 5

Charles Bretana
Charles Bretana

Reputation: 146499

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).

Upvotes: 2

Related Questions