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)
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)
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)
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.
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With