CODEWITHSUNDEEP
mysqlgreatest-n-per-group
Junior
Junior

Reputation: 557

MySQL - using one value from each group using some conditions

I have a table like this in MySQL. I want to calculate average of values (column Value) but not using all rows. From each group (Column Group) I want to use only one value, with the biggest rank (Column Rank) if there are multiple rows in that group.

+----+-------+-------+------+
| ID | Value | Group | Rank |
+----+-------+-------+------+
|  1 |    10 |     1 |    1 |
+----+-------+-------+------+
|  2 |     9 |     1 |    2 |
+----+-------+-------+------+
|  3 |     7 |     2 |    2 |
+----+-------+-------+------+
|  4 |    10 |     2 |    1 |
+----+-------+-------+------+
|  5 |    11 |     3 |    1 |
+----+-------+-------+------+
|  6 |     9 |     4 |    1 |
+----+-------+-------+------+
|  7 |     8 |     5 |    1 |
+----+-------+-------+------+
|  8 |    10 |     6 |    2 |
+----+-------+-------+------+
|  9 |     9 |     7 |    1 |
+----+-------+-------+------+

So, in group 1 I must use value 9 from row ID 2 because it has the biggest rank. In group 2 I will use value 7 from row ID 3 because it has the biggest rank. And the rest I will use the only values because there are no alternatives. In the end I want to calculate the average of values from rows 2, 3, 5, 6, 7, 8, 9. How can I do that in one query?

Upvotes: 1

Views: 87

Answers (1)

forpas
forpas

Reputation: 164069

You can use NOT EXISTS to filter only the rows with the highest rank from each group:

SELECT AVG(t1.Value) avg_value
FROM tablename t1
WHERE NOT EXISTS (
  SELECT 1
  FROM tablename t2
  WHERE t2.group = t1.group AND t2.rank > t1.rank
)

Or, with a correlated subquery:

SELECT AVG(t1.Value) avg_value
FROM tablename t1
WHERE t1.rank = (SELECT MAX(t2.rank) FROM tablename t2 WHERE t2.group = t1.group)

See the demo.

Upvotes: 1

Related Questions