postgres: Combining filter() with a percentile_cont()

Question

Trying to use a filter() clause within a call to percentile_const and I am not sure I can do this. Is there a way? Here's the example query:

select
    count(*) as n1,
    count(*) filter(where ha >= 0) as n2,
    percentile_cont(.9) within group (order by es asc) as p1,
    percentile_cont(.9) filter (where ha >= 0) within group (order by es asc) as p2
from mytable where mypid = 123;

The query works fine without the p2 call of course, but you can see what I want to do.

Answer 1

The filter needs to go after the within group part:

select
    count(*) as n1,
    count(*) filter(where ha >= 0) as n2,
    percentile_cont(.9) within group (order by es asc) as p1,
    percentile_cont(.9) within group (order by es asc) filter (where ha >= 0) as p2
from mytable 
where mypid = 123;