CODEWITHSUNDEEP
postgresql
Wells
Wells

Reputation: 10969

postgres: Combining filter() with a percentile_cont()

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.

Upvotes: 4

Views: 1682

Answers (1)

user330315
user330315

Reputation:

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;

Upvotes: 6

Related Questions