CREATE TABLE t1 ( a INT, b INT, c INT, KEY (a, b) ); INSERT INTO t1 VALUES ( 1, 1, 1 ), ( 1, 2, 2 ), ( 1, 3, 3 ), ( 1, 4, 4 ), ( 1, 5, 5 ), ( 1, 9, 13 ), ( 2, 1, 6 ), ( 2, 2, 7 ), ( 2, 3, 8 ); EXPLAIN SELECT a, AVG(t1.b), (SELECT t11.c FROM t1 t11 WHERE t11.a = t1.a AND t11.b = AVG(t1.b)) AS t11c, (SELECT t12.c FROM t1 t12 WHERE t12.a = t1.a AND t12.b = AVG(t1.b)) AS t12c FROM t1 GROUP BY a; id select_type table type possible_keys key key_len ref rows Extra 1 PRIMARY t1 index NULL a 10 NULL 9 Using index 3 DEPENDENT SUBQUERY t12 ref a a 10 t1.a,func 2 Using where 2 DEPENDENT SUBQUERY t11 ref a a 10 func,func 2 Using where SELECT a, AVG(t1.b), (SELECT t11.c FROM t1 t11 WHERE t11.a = t1.a AND t11.b = AVG(t1.b)) AS t11c, (SELECT t12.c FROM t1 t12 WHERE t12.a = t1.a AND t12.b = AVG(t1.b)) AS t12c FROM t1 GROUP BY a; a AVG(t1.b) t11c t12c 1 4.0000 4 4 2 2.0000 7 7 DROP TABLE t1;