Where clause inside an over clause in postgres

user2569524 picture user2569524 · Mar 3, 2014 · Viewed 7.1k times · Source

Is it possible to use the where clause inside an overclause as below ?

SELECT SUM(amount) OVER(partition by prod_name WHERE dateval > dateval_13week)

I cannot use preceding and following inside over clause as my dates are not in the order. All I need to fetch is the records that are less than 13week date value of the current record.

EDIT : 
sum(CASE WHEN dateval >= dateval_13week and dateval <=current_row_dateval then amount else 0 end) over (partition by prod_name order by week_end desc)

Just to elaborate, earlier I was partitioning the records with the below query when I had all my dates in a sequence. Now I have the dates in random order and there are some missing dates.

sum(amount) over 
        (partition by prod_name order by prod_name,week_end desc rows between 0 preceding and 12 following)

Answer

Adding to @D Stanley answer you can use FILTER clause for aggregate function in Postgre:

SELECT SUM(amount) FILTER (WHERE dateval > dateval_13week)
           OVER(partition by prod_name)