How to fix "ERROR: aggregate functions are not allowed in UPDATE"

Question

I am trying to update a column based on a few conditions, using a calculation.

The theory I am using is as follows; If column1 contains 'string' then 'calculation of column2 and column3' gets put in column4.

The calculation works, but I am struggling to find a way to UPDATE a column by using these IF conditions and a SUM.

I have searched stack and postgres documentation. I see that there are a number of aggregate errors but none specifically solve this problem.

UPDATE table1
SET "column4" = CASE
        WHEN "column1" ILIKE '%Y%' THEN SUM(CAST("column2" AS 
                numeric(4,2))) / SUM(CAST("column3" AS numeric(4,2)))
        END;

The error which I am getting is as follows;

ERROR: aggregate functions are not allowed in UPDATE LINE 7: WHEN "column1" ILIKE '%Y%' THEN (SUM(CAST("...

Answer 1

Do your calculations in a common table expression:

    WITH cte_avg AS (
        SELECT SUM(CAST("column2" AS numeric(4,2))) / SUM(CAST("column3" AS numeric(4,2))) AS avg
        FROM table1
    )
    UPDATE table1
    SET "column4" = cte_avg.avg
    FROM cte_avg
    WHERE "column1" LIKE '%Y%'