PostgreSQL: Syntax error using the filter clause

Question

In one section of my query, I wish to compute the average time spent per student per exam. However, I'm having syntax difficulties in knowing where to place "filter".

Normally, placing it just after the aggregation function works - e.g.

count(distinct student_id) filter (where student_id is not null or student_id not in ('A', 'C', 'Z'))

However, the following doesn't work. I keep on moving it around and no luck yet. Could I have some explanation on how to do it right? Thanks in advance!

 ( sum(extract(epoch from time_spent)) / count(distinct exam_id)::float )/60
          filter (where student_id is not null or student_id not in ('A', 'C', 'Z'))
     as avg_time_min,

Answer 1

Consider running FILTER() on both aggregates of the expression and adjust casting to after calculation

SELECT ...
       
       sum(extract(epoch from time_spent)) filter (
           where student_id is not null or student_id not in ('A', 'C', 'Z')
       )          
       /        
       count(distinct exam_id) filter (
           where student_id is not null or student_id not in ('A', 'C', 'Z')
       )::float/60 
       
       AS avg_time_min
       ...

FROM my_table