enter image description hereHelp for a newer SQL analyst: I need to get MAX value from the YearMo column by company, where Prem is zero/null. I created a flag to find YearMo where the sum is 0, indicated by a '1' in the exclude column.
How can I edit my current window function to include a case statement where it would not include YearMo's that have the flag.
The function: MAX("YearMo") OVER (PARTITION BY "Company") AS "MaxYearMo"
CodePudding user response：
Using CASE expression to build conditional aggregation. "where Prem is zero/null" :
MAX(CASE WHEN Prem IS NULL OR Prem = 0 THEN "YearMo" END)
OVER (PARTITION BY "Company") AS "MaxYearMo"
otherwise:
MAX(CASE WHEN Prem <> 0 THEN "YearMo" END)
OVER (PARTITION BY "Company") AS "MaxYearMo"