14

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)
user2569524
  • 1,651
  • 7
  • 32
  • 57

3 Answers3

19

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)
Алексей Виноградов
  • 1,005
  • 13
  • 10
10

You could simulate the WHERE in your SUM parameter:

SELECT SUM(CASE WHEN dateval > dateval_13week THEN amount ELSE 0 END) 
           OVER(partition by prod_name)
D Stanley
  • 149,601
  • 11
  • 178
  • 240
  • 1
    @D Stanley . Could you please check my EDIT now ? I want the sum of all the entries that are between my current row date and its date before 13weeks. Not just comparing to its 13week value. My current row date value is my reference to fetch the entries – user2569524 Mar 03 '14 at 15:33
1

You cannot filter the rows with the WHERE clause, inside the OVER partition clause. You can fix the query selecting only the rows that are needed to you, using CASE and performing a sum of the amount where the condition is satisfied.

Alberto Solano
  • 7,972
  • 3
  • 38
  • 61