Getting avg without counting hits twice

Question

I have two tables that are linked in a 1:n relationship. I want to get the average(value) for all rows in a that have corresponding entries in b. However, if there are multiple rows in b for a row in a, these are obviously counted multiple times. How can I avoid this?

mysql> select * from a;
+----+-------+
| id | value |
+----+-------+
|  1 |     1 |
|  4 |     0 |
+----+-------+

mysql> select * from b;
+------+
| aid  |
+------+
|    1 |
|    1 |
|    4 |
+------+

mysql> select avg(value) from a,b where a.id=b.aid;
+------------+
| avg(value) |
+------------+
|     0.6667 |
+------------+

The last result should be 0.5, as there are two rows in a with values 0 and 1 that have a value in b.

Answer 1

Just distinct the aid values before you join. The syntax could be made neater, but this works:

select avg(value) from a,(select distinct aid from b) b where a.id=b.aid;

Answer 2

Instead of join operation you could use a subquery:

SELECT AVG(value) FROM a
WHERE id IN (SELECT DISTINCT aid FROM b)