how to filter with having clause in window function over avg?

Question

I created a fiddle http://sqlfiddle.com/#!17/f882b/23.

It has table 'scots' with name, age, country, weight.

select name, weight, country,
avg(weight) over (order by name) as avg_weight_over_computed
from scots 
-- group by country
having avg_weight_over_computed > 76
order by name

This above not working with "having" clause. I want to filter those avg_weights > 76. How to do this?

Answer 1

In this case, the HAVING could be converted to a where clause in a subquery?

SELECT * FROM
(
  select 
    name, 
    weight, 
    country,
    avg(weight) over (order by name) as avg_weight_over_computed
  from 
      scots 
)
AS X
where 
  avg_weight_over_computed > 76