Fill in null values in outer join

[boardtc]

I am joining a select statement and a subquery with a left outer join. The subquery does a an average on one of the selects columns grouped by name, and I only select this sum value in the resultant joined result set. Typically there will the select result set will be 10 times bigger than the subquery.

For all the nulls I want to show the average value obtained from the subquery for that group.

TableA
---------
name        metric           value
name1     metric1.1       5
name1     metric1.2       4
name1     metric1.3       9
name2     metric2.1       2
name2     metric2.2       4
 
select a.name,a.metric,a.value,b.average
from tablea a
left outer join (
select name,metric,round(avg(value)) as average
from tablea
group by name
) b
on
a.name=b.name and
a.metric=b.metric

gives:

name        metric           value   average
name1     metric1.1       5         18 
name1     metric1.2       4
name1     metric1.3       9
name2     metric2.1       2         3
name2     metric2.2       4

I want

name        metric           value   average
name1     metric1.1       5         18 
name1     metric1.2       4         18
name1     metric1.3       9         18
name2     metric2.1       2         3
name2     metric2.2       4         3

How can I achieve this?

Thanks, Tom.

[boardtc]

Of course the answer was to reduce the join criteria by eliminating the
a.metric=b.metric