Alternatives to returning 0 if value is null in postgreSQL/Redshift [duplicate]

I currently have a query that is returning a zero if the value is null, and I am wanting to find alternative ways of writing this so that it works in PostgreSQL/redshift.

 coalesce(sum(Score)/nullif(sum(ScorePrem),0),0) as percent 

Please provide me with alternatives which will help me get around the error of nested aggregates

1 Answer

You could try using a case expression:

(case when sum(ScorePrem) = 0 then 0 else sum(Score) / sum(ScorePrem) end) 

However, your code should work.

EDIT:

You have at least one problem in the second query. You are using = to assign an alias. But in Redshift that is just a boolean expression -- and one not in the GROUP BY. So:

SELECT calendar_month_id, day_of_month, month_name, DaysRemaining, 'Entire' as RPTBRANCH, 1 as TotalGrp, sum(Score) as score, SUM(ScorePrem) as ScorePrem, coalesce(sum(Score)/nullif(sum(ScorePrem),0),0) as percent FROM #temp_score GROUP BY calendar_month_id, day_of_month, month_name, DaysRemaining